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

    Calculation

    Annette

      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
          MarcMcCall

               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
            philmodjunk

                 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
              LaRetta_1

                   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" )
                   ) )

                   I hope this is helpful.

              • 4. Re: Calculation
                Annette

                      Thanks so much all of you for your help.  You all make it look so easy, can't wait till I can wrap my head around calculations and scripts!!

                      

                     Again, thanks!