4 Replies Latest reply on Oct 18, 2012 1:44 AM by Annette

# Calculation

### Title

Calculation

### Post

Hi,

I'm trying to do a time calculation that gives me a result in hours and minutes between a start time and an end time.

I have the following fields:

•           Start Time
•
•           End Time
•
•           Total
•
•           Appt Status

The appt status field could be Attended, Cancelled, DNA, etc.  What I would like to do is say if the appt start is 3:00and end time 4:00 the total to return 1 hour 0 minutes.  But, if the status says DNA or Cancelled then it to return 0. (They will never be on different days so I don't need to add the start / end date to the calculation)

I tried to figure out the calculation but it is not working.  I'm still new to trying to figure out calculations and things so could anyone help me determine where I went wrong?

Case (  not ActivityStatus = "DNA" or not ActivityStatus = "Cancelled"; Hour (ActivityTimeEnd-ActivityTimeStart) & (If (Hour (ActivityTimeEnd- ActivityTimeStart) = 1; " hour, "; " hours, ")) & Minute (ActivityTimeEnd - ActivityTimeStart) & (If (Minute (ActivityTimeEnd - ActivityTimeStart) = 1; " minute "; " minutes ")))

Thanks for the help.

• ###### 1. Re: Calculation

Try this,

Case(ApptStatus   =   "DNA" or ApptStatus   =   "Cancelled";"";Hour (EndTime-StartTime) & (If (Hour (EndTime- StartTime) = 1; " hour, "; " hours, ")) & Minute (EndTime - StartTime) & (If (Minute (EndTime - StartTime) = 1; " minute "; " minutes ")))

• ###### 2. Re: Calculation

I'd use:

if ( PatternCount ( "DNACancelled" ; ApptStatus ) ; 0 ; End Time - Start Time )

And select Time as the return type so that I can display the results as: HH:MM

• ###### 3. Re: Calculation

Hi Annette  :^)

In addition to Marc's good example, here is another way of writing it.  It protects from improper display (in case the Appt Status = "Other" and there is no start or end) or if end is less than start.  We do not need a start time for the calculation to work.  Note that I used a space between as was originally requested.

This version drops the hours or minutes if there aren't any.  Displays as: 22 minutes or 1 hour 22 minutes.

Case ( Appt Status  =  "Cancelled" or Appt Status  =  "DNA" ; 0 ;
End Time ≥ Start Time ;
Let ( [
t = End Time - Start Time ;
h = Hour ( t ) ;
m = Minute ( t )
] ;
Trim ( If ( h ; h & " hour" & If ( h  ≠ 1 ; "s" ) ) &
If ( m ; " " & m & " minute" & If ( m  ≠  1 ; "s" ) )
) ) )

Shows both hours and minutes regardless.  Displays as: 0 hours 15 minutes or 5 hours 0 minutes or even 0 hours 0 minutes

Case ( Appt Status  =  "Cancelled" or Appt Status  =  "DNA" ; 0 ;
End Time ≥ Start Time ;
Let ( [
t = End Time - Start Time ;
h = Hour ( t + 0 )  ;
m = Minute ( t + 0 )
] ;
h & " hour" & If ( h  ≠ 1 ; "s" )  & " " & m & " minute" & If ( m  ≠  1 ; "s" )
) )