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