Help Needed - Two DateTime Duration to exclude non working hours

Have checked out the forum posts and documentation, can’t seem to find the right way to do the duration calculation for two datetime to exclude non working hours (weekend, after office or PH)

any one has any idea how we can do it.

IF(
    (AND(DATETIME([Respond]) <= (DATETIME(DATE([Case])) 
      	 + ("18:00:00" - "00:00:00")),
	       DATETIME([Respond]) >= (DATETIME(DATE([Case])) 
         + ("09:00:00" - "00:00:00"))
         )
    ), 
    ([Respond] - [Case]),
<< NEED HELP HERE>>
   )

Duration Sample App
the working hour is 9AM - 6PM, it calculate the working hours (duration) between two dates

Make the App Public for copy - Working Hours Duration - AppSheet

Test cases
Case - 04/06/2021 4PM
Resp - 04/06/2021 10PM
Result - 2 Hours - Pass

Case - 04/06/2021 4AM
Resp - 04/06/2021 10AM
Result - 1 Hours - Pass

Case - 04/06/2021 9AM
Resp - 04/06/2021 12AM
Result - 3 Hours - Pass

Case - 04/06/2021 9AM
Resp - 05/06/2021 12AM
Result - 18 Hours - Failed (Correct 12 Hours)

Question:
Why this getting NaN:NaN:NaN if they hours is more than 27?

Here is the Duration Formula,

IFS(
		AND(DATETIME([Respond]) <= (DATETIME(DATE([Case])) + ("18:00:00" - "00:00:00")),
	      DATETIME([Case]) >= (DATETIME(DATE([Respond])) + ("09:00:00" - "00:00:00"))
			 ) , 
		([Respond] - [Case]) ,
            

		AND(DATETIME([Respond]) > (DATETIME(DATE([Case])) + ("18:00:00" - "00:00:00")),
	      DATETIME([Case]) < (DATETIME(DATE([Respond])) + ("09:00:00" - "00:00:00"))
			 ) ,
    (TIME(CONCATENATE(( (TOTALHOURS((DATE([Respond]) - DATE([Case]) 
    - (DATE(WORKDAY(Date([Case]), ROUND(TOTALHOURS((DATE([Respond]) 
    - DATE([Case]))) / 24 ))) - DATE([Respond])))  )) / 24 * 9 ),":00:00")) 
    - "00:00:00") + ("09:00:00" - "00:00:00"),  
			 
		DATETIME([Respond]) > (DATETIME(DATE([Case])) + ("18:00:00" - "00:00:00")) ,
    (TIME(CONCATENATE(( (TOTALHOURS((DATE([Respond]) - DATE([Case]) 
    - (DATE(WORKDAY(Date([Case]), ROUND(TOTALHOURS((DATE([Respond]) 
    - DATE([Case]))) / 24 ))) - DATE([Respond])))  )) / 24 * 9 ),":00:00")) 
    - "00:00:00") + ((DATE([Respond]) + ("18:00:00" - "00:00:00")) - [Case]) ,
			
 
	  DATETIME([Case]) < (DATETIME(DATE([Respond])) + ("09:00:00" - "00:00:00")) ,
    (TIME(CONCATENATE(( (TOTALHOURS((DATE([Respond]) - DATE([Case]) 
    - (DATE(WORKDAY(Date([Case]), ROUND(TOTALHOURS((DATE([Respond]) 
    - DATE([Case]))) / 24 ))) - DATE([Respond])))  )) / 24 * 9 ),":00:00")) 
    - "00:00:00") + ([Respond] - (DATE([Case]) + ("09:00:00" - "00:00:00"))) 
        

   )

Here the portion of the code that facing the problem, looks like it is a bug, when we se to

01 June 2021 4AM (Case) to 02 June 2021 10PM (Respond) it is displaying correct at different at 9 working hours. if you continue to add one day to the Respond, it will continue to add 9 hours, until more than three days it will throw and error NaN:NaN:NaN

DATETIME([Respond]) > (DATETIME(DATE([Case])) + (“18:00:00” - “00:00:00”)) ,
(TIME(CONCATENATE(( (TOTALHOURS((DATE([Respond]) - DATE([Case])
- (DATE(WORKDAY(Date([Case]), ROUND(TOTALHOURS((DATE([Respond])
- DATE([Case]))) / 24 ))) - DATE([Respond]))) )) / 24 * 9 ),":00:00"))
- “00:00:00”) + ((DATE([Respond]) + (“18:00:00” - “00:00:00”)) - [Case]) ,

Reformatted for my clarity:

IFS(
  AND(
    ([Respond] <= (DATETIME(DATE([Case])) + "018:00:00")),
    ([Case] >= (DATETIME(DATE([Respond])) + "009:00:00"))
  ),
    ([Respond] - [Case]),
  AND(
    ([Respond] > (DATETIME(DATE([Case])) + "018:00:00")),
    ([Case] < (DATETIME(DATE([Respond])) + "009:00:00"))
  ),
    (
      (
        TIME(
          CONCATENATE(
            (
              TOTALHOURS(
                DATE([Respond])
                - DATE([Case])
                - (
                  DATE(
                    WORKDAY(
                      Date([Case]),
                      ROUND(TOTALHOURS(DATE([Respond]) - DATE([Case])) / 24)
                    )
                  )
                  - DATE([Respond])
                )
              )
              / 24 * 9
            ),
            ":00:00"
          )
        )
        - "00:00:00"
      )
      + "009:00:00"
    ),
  ([Respond] > (DATETIME(DATE([Case])) + "018:00:00")),
    (
      (
        TIME(
          CONCATENATE(
            (
              TOTALHOURS(
                DATE([Respond])
                - DATE([Case])
                - (
                  DATE(
                    WORKDAY(
                      Date([Case]),
                      ROUND(TOTALHOURS(DATE([Respond]) - DATE([Case])) / 24)
                    )
                  )
                  - DATE([Respond])
                )
              )
              / 24 * 9
            ),
            ":00:00"
          )
        )
        - "00:00:00"
      )
      + (
        (DATE([Respond]) + "018:00:00")
        - [Case]
      )
    ),
  ([Case] < (DATETIME(DATE([Respond])) + "009:00:00")),
    (
      (
        TIME(
          CONCATENATE(
            (
              TOTALHOURS(
                DATE([Respond])
                - DATE([Case]) 
                - (
                  DATE(
                    WORKDAY(
                      Date([Case]),
                      ROUND(TOTALHOURS(DATE([Respond]) - DATE([Case])) / 24)
                    )
                  )
                  - DATE([Respond])
                )
              )
              / 24 * 9
            ),
            ":00:00"
          )
        )
        - "00:00:00"
      )
      + (
        [Respond]
        - (DATE([Case]) + "009:00:00")
      )
    )
)

Took over 45 minutes just to reformat that.

2 Likes

These are the four IFS() conditions, as I’ve been able to make sense of them:

IFS(
  AND(
    ([Respond] <= (DATETIME(DATE([Case])) + "018:00:00")),
    ([Case] >= (DATETIME(DATE([Respond])) + "009:00:00"))
  ),
    ...,
  AND(
    ([Respond] > (DATETIME(DATE([Case])) + "018:00:00")),
    ([Case] < (DATETIME(DATE([Respond])) + "009:00:00"))
  ),
    ...,
  ([Respond] > (DATETIME(DATE([Case])) + "018:00:00")),
    ...,
  ([Case] < (DATETIME(DATE([Respond])) + "009:00:00")),
    ...
)

My plain-language interpretation of each is:

  1. The response occurred before 6pm on the case date and the case occurred after 9am on the response date. In this case, the response occurred between 9am and 6pm of the case date. This would catch a case and its response both occurred within normal business hours of the same day. This would not catch a case that occurred during normal business hours with a response that occurred during normal business hours of a different day.

  2. The response occurred after 6pm on the case date and the case occurred before 9am on the response date. In this case, the case occurred before 9am (outside normal business hours) but the response occurred after 6pm of the case day (after business hours the day the case occurred, perhaps meaning a late response?). This would catch a case with a response that occurred at any time after 6pm on the date of a case that occurred before 9am. This condition seems odd the me.

  3. The response occurred after 6pm of the case date, whether the response itself occurs inside or outside normal business hours. This would catch the case where a case occurred inside normal business hours but the response occurred at anytime after 6pm of the case date, even if the response occurred during normal business hours (for instance, during normal business hours the following day).

  4. The case occurred before 9am on the response date, regardless when the response occurred. This would catch a case that occurred at any time before 9am on the day the response occurred (for instance, if the case was logged the day before the response occurred).

My sense is your conditions probably don’t cover all possible situations. At the very least, they’re hugely confusing.

2 Likes

Thanks @Steve sorry about the trouble…

1 Like

What I am doing is to calculate the duration of the two DateTime by excluding the non-working hours. (weekend, non-working hours, or PH)

This for Respond time SLA calculation, where by there will be a [Case] logged DateTime, and a [Respond] DateTime. By calculate the work hours duration of [Respond] & [Case], we are able to do a Respond time SLA measurement, to see if the duration of [Respond] & [Case] is within the SLA defined.

I searched the forum, the closest example we have is @Steve example - Compute work time using workday to compute the work time however i see the calculating the two DateTime duration in working hours is a lot more complication. (probably that’s what i feel)

The 4 IFS() cases are the 4x scenario in the Work hours duration formulation. (where working hours is 9AM to 6PM)

  1. Both [Case] & [Respond] is within the working hours

    • example [Case] 10AM, [Respond] 5PM
    • this will be the simple case [Respond]-[Case] at 07:00:00 (7 hours in duration)
  2. Both [Case] & [Respond] is out of the working hours.

    • example [Case] logged at 8AM, [Respond] engineer respond it at 7PM
    • this will be the out of working hours, will not be calculated. hence it will be 09:00:00 (9 hours in duration)
  3. [Case] is out of working hours, [Respond] is within working hours

    • example [Case] logged at 6AM, [Respond] engineer respond it at 5PM
    • it will take [Respond] 5PM - [Case] @ 9AM at 08:00:00 (8 hours in duration)
  4. [Case] is within working hours, [Respond] out of working hours

    • example [Case] logged at 10AM, [Respond] engineer respond it at 9PM
    • it will take [Respond] 6PM - [Case] @ 10AM at 08:00:00 (8 hours in duration)

Not sure if the above cases is necessary or we can do without the cases above. at the same time, we want to exclude the weekend and PH. (which i think we might to use workday) however workday is calculate the next working day but not the duration of the working day.

Using the below formula to calculate the “duration of the working day”, not sure if it is making sense, but it some what return the right duration of the working day but notice there is problem with it.

      (
        TIME(
          CONCATENATE(
            (
              TOTALHOURS(
                DATE([Respond])
                - DATE([Case])
                - (
                  DATE(
                    WORKDAY(
                      Date([Case]),
                      ROUND(TOTALHOURS(DATE([Respond]) - DATE([Case])) / 24)
                    )
                  )
                  - DATE([Respond])
                )
              )
              / 24 * 9
            ),
            ":00:00"
          )
        )
        - "00:00:00"
      )

@June_Corpuz is helping me with the formula, will share the code the community once it is ready. feel free to participate to put in the case/scenario to test the formula.

The pre set condition is working hours is 9AM to 6PM, and non working hours, weekend and PH will not be part of the duration calculation.

You may help to set the start, end time and the expected duration to see if the formula is correct.