Help Needed - Two DateTime Duration to exclude non working hours

[EugeneB]

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

Compute work time Tips & Tricks 💡

Given a start date & time (DateTime column) and a number of work hours (Hours column, type Decimal), calculates the finish date & time to the nearest hour. Assumes the work day starts at 8:00 AM ("08:00:00") and ends at 5:00 PM ("17:00:00"). Uses WORKDAY() to compute dates. IF( ( ([DateTime] + ("000:00:00" + CEILING([Hours]))) <= (DATETIME(DATE([DateTime])) + ("17:00:00" - "00:00:00")) ), ([DateTime] + ("000:00:00" + ROUND([Hours]))), ( DATETIME( WORKDAY( DATE(…

Overtime work based on Duration after 05 PM and Before am Questions

Hi Appsheet Community, I am busy creating an app which calculates the overtime work. Thus far I have my duration calculating the duration between ColumnSTART and ColumnEND but, I need to exclude any time between 6AM and 5PM as this is not part of the overtime. Furthermore an employee can start working on the 1st of the month and only end a few days later. Does someone have some idea of how to work out a duration over a period but exclude as certain portion of the day?

help.appsheet.com

WORKDAY()

Computed date excluding non-work days

[EugeneB]

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

   )

[Steve]

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.

[EugeneB]

Thanks @Steve sorry about the trouble…

[Steve]

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.

[EugeneB]

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

[EugeneB]

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]) ,

[EugeneB]

@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.

AppSheet

AppSheet

Create mobile apps for your business in just minutes. No code, no delay, no cost.

[EugeneB]

It is a lot complicated than what we anticipated, seem that “duration for WORKDAY()” is pretty tough

@Steve hope there will be “duration for WORKDAY()”

[EugeneB · Accepted solution]

The app is ready, feel free to take a look

AppSheet

AppSheet : mobile apps from spreadsheets : Duration Testing App - AppSheet

How to build apps for Duration Testing App using your data in Google Drive, Dropbox, Office 365, and more.

[EugeneB]

Not sure if anyone has the problem when set duration as 36:00:00 and 27:00:00. it changed to the number below, anyone has any idea why it is like that

36:00:00 → 1060824:00
27:00:00 → -15559272:00

it is a bug?

[Suvrutt_Gurjar]

I have not looked at the expression but below is based on the details you have shared.
I think somewhere in the expression a “DateTime” value is getting subtracted from only a Time Value
for example

“09:00”- “06/13/2021 10:00”

Please try above in a test field. A time of “09:00” resolves to a date time of “12/30/1899 9:00:00 AM” meaning a date time in the year 1899. The subtraction then results in very high number of hours.

[EugeneB]

@Suvrutt_Gurjar Thanks for the reply, the Expected Duration column just a Duration type, where we insert the duration manually.

however when enter 27:00 or 36:00 it display
36:00:00 → 1060824:00
27:00:00 → -15559272:00

[Suvrutt_Gurjar]

Hi @EugeneB ,

Oh okay, got it, thank you. I tested in the app shared by you ( by copying it) as well as in a separate test app of my own.

I did not face the problem mentioned by you. In fact, there were existing readings of 27 and 36 hours in the copied app shared by you.

[EugeneB]

you give me a idea, i changed the excel formatting into text. seem that this workaround fixed the issue.
is your excel column formatting as text as well?

earlier it was set to h:mm:ss

[Suvrutt_Gurjar]

Hi @EugeneB ,

I used Google Sheets. Anyway, it sounds that your approach should be good, if it is working. At times , mismatches in spreadsheet column type settings and in app column setting cause issues in field display.

[Steve]

In general, automatic formatting is the riskiest and will cause problems in some cases. Plain text format is the safest, but provides no localization within the spreadsheet, which could cause confusion for those who use the spreadsheet directly (which should be avoided anyway). Otherwise, choose a specific, data-appropriate format for the spreadsheet column you want formatted.

[freelancer79]

Hello I need help with this. I also need to include a 30 minute break from 11.45 AM to 12.15PM. Please help.