Overtime work based on Duration after 05 PM and Before am

[Louwrens]

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?

[LeventK]

Aaaarrhgghhhh…My eyessss killing me From your post I have read the [Time Out] as 08:01:32 AM and [Time In] as 08:03:32 AM

@Louwrens
I have slightly edited my expression in post#40 above. Can you please try with that and feedback me? Thnx.

[Louwrens]

Thanks will run a few test and revert

[Louwrens]

Thanks for the effort over the last 2 weeks.

The formulation / Expression is 100 %.

[LeventK]

You’re welcome @Louwrens and thanks for the feedback. I’m glad to hear that it’s working 100% now.

[LeventK]

@Louwrens
Can you test with this pls?

IF(
	DATE([TIME IN]) = DATE([TIME OUT]),
	IFS(
		AND(
			TIME([TIME OUT]) >= TIME("00:00:00"),
			TIME([TIME OUT]) <= TIME("06:00:00")
		),
		IFS(
			TIME([TIME IN]) <= TIME("06:00:00"),
			TOTALHOURS([TIME IN] - [TIME OUT])*1.00,
			AND(
				TIME([TIME IN]) > TIME("06:00:00"),
				TIME([TIME IN]) <= TIME("17:00:00")
			),TOTALHOURS(DATETIME(DATE([TIME IN])&" "&TIME("06:00:00")) - [TIME OUT])*1.00,
			TRUE,(TOTALHOURS(DATETIME(DATE([TIME IN])&" "&TIME("06:00:00")) - [TIME OUT]) + TOTALHOURS([TIME IN] - DATETIME(DATE([TIME IN])&" "&TIME("17:00:00"))))*1.00
		),
		TIME([TIME OUT]) >= TIME("06:00:00"),
		IF(
			TIME([TIME IN]) < TIME("17:00:00"),
			0,
			TOTALHOURS([TIME IN] - DATETIME(DATE([TIME IN])&" "&TIME("17:00:00")))*1.00
		),
		AND(
			TIME([TIME OUT]) >= TIME("17:00:00"),
			TIME([TIME IN]) <= TIME("23:59:59")
		),TOTALHOURS([TIME IN] - [TIME OUT])*1.00
	),
	TOTALHOURS(
		IF(
			TIME([TIME IN]) >= TIME("06:00:00"),
			DATETIME(DATE([TIME IN])&" "&TIME("06:00:00")),
			[TIME IN]
		) - 
		IF(
			TIME([TIME OUT]) >= TIME("17:00:00"),
			[TIME OUT],
			DATETIME(DATE([TIME OUT])&" "&TIME("17:00:00"))
		)
	)*1.00
)

[EugeneB]

@LeventK Great work on logic and expression. just curious this will not take into consideration of non-working day (or PH) right? was thinking WORKDAY() but however it doesn’t do duration. WORKDAY() just a DATETIME which give you the next WORKDAY based on the input.

Do you think it is possible to calculate duration to exclude non-working day. (SAT and SUN) which i think you have asked before. which i think that’s should be challenging right.