Overtime work based on Duration after 05 PM and Before am

LeventK · December 29, 2020, 4:19pm

Gotcha. May I humbly ask, how would you calculate this:
Start 12/28/2020 17:00:00
End 12/30/2020 02:30:00

Should the calculation be like?

= ([12/29/2020 06:00] - [12/28/2020 17:00]) + ([12/30/2020 02:30:00] - [12/29/2020 17:00])> = (13) + (9.5)> = 22.5

And what’s the situation shall be if the gap is more? Like:
Start 12/26/2020 17:00:00
End 12/30/2020 02:30:00
Should the calculation be like?

= ([12/27/2020 06:00] - [12/26/2020 17:00]) + ([12/28/2020 06:00]-[12/27/2020 17:00]) + ([12/29/2020 06:00] - [12/28/2020 17:00]) + ([12/30/2020 02:30:00] - [12/29/2020 17:00])> = (13) + (13) + (13) + (9.5)> = 48.5

Louwrens · December 30, 2020, 6:55am

I do not need to calculate Normal hours -
Overtime is hours calculated in the same fashion as after hours.
I drew a sketch which might help.

Total Hours worked: 15:00:00 - 08:00:00 = 17 Hours
Total overtime worked: 17:00:00 - 06:00:00 = 13 Hours

Hope this helps.

LeventK · January 1, 2021, 12:33am

@Louwrens
Do apologize for my late reply. As per your given explanation, description and instructions; the expression to calculate the Overtime is:

TOTALHOURS(
	IF(
		HOUR(TIME([END]) - "00:00:00") > 6,
		DATETIME(DATE([END])&" "&TIME("06:00:00")),
		[END]
	) - 
	IF(
		HOUR(TIME([START]) - "00:00:00") > 17,
		DATETIME(DATE([START])&" "&TIME("17:00:00")),
		[START]
	)
)

Louwrens · January 4, 2021, 4:06pm

Good day @LeventK.

Thanks for the effort. It seems like the formula is working but, as soon as the overtime stops the same day it started the calculation is wrong (-11 hours )

Example:

LeventK · January 4, 2021, 4:16pm

@Louwrens
In your handnote, you had been explicitly specified at the end: Start and end times are never the same. I remember that I have asked this situation many times on purpose in my prior posts.

Louwrens · January 4, 2021, 4:27pm

Yes you’re correct.

No two jobs will ever start and end on the same time.
Job A can start @ 10am and end @ 02 am the following day and
Job B can start @ 5 pm and end the same day @ 11 pm

Never did I say anything about the dates.
I did not say Sart and End dates are never the same.

Sorry for the misconfusion.

LeventK · January 4, 2021, 4:53pm

Sorry but couldn’t get you and confused here. Try with this please:

IF(
	DATE([END]) = DATE([START]),
	(IFS(
		HOUR(TIME([END]) - "00:00:00") > 17,
		TOTALHOURS([END] - DATETIME(DATE([END])&" "&TIME("17:00:00"))),
	) + 
	IFS(
		HOUR(TIME([START]) - "00:00:00") < 6,
		TOTALHOURS(DATETIME(DATE([START])&" "&TIME("06:00:00")) - [START]),
	)),
	TOTALHOURS(
		IF(
			HOUR(TIME([END]) - "00:00:00") > 6,
			DATETIME(DATE([END])&" "&TIME("06:00:00")),
			[END]
		) - 
		IF(
			HOUR(TIME([START]) - "00:00:00") > 17,
			DATETIME(DATE([START])&" "&TIME("17:00:00")),
			[START]
		)
	)
)

Louwrens · January 5, 2021, 8:38am

The formula seems to work if the dates are the same but, as soon as the date goes over to the next day it formulates something totally different.

Louwrens · January 6, 2021, 12:12pm

Good day @LeventK,

Do you have any solution for this or maybe another approach?

Thanks for the help.

LeventK · January 6, 2021, 12:52pm

@Louwrens
I have just added a control to the beginning of my original expression to verify if the start and end dates are the same. So if the expression is working for the same start and end dates, it should work as well for the cases when Date[End] > Date[Start] because prior you had said that it was working for different dates but not working for the same days. Please elaborate the issue with some exemplary scrshots etc.

Louwrens · January 6, 2021, 1:56pm

Would it be possible to share the app with you as an editor?

LeventK · January 6, 2021, 2:58pm

@Louwrens
Sure thing. You can share the app with levent@able3ventures.com. Please share the gSheet as well temporarily with edit access. Thnx.

Louwrens · January 6, 2021, 3:10pm

Thanks @LeventK

LeventK · January 6, 2021, 5:55pm

@Louwrens
I believe I have fixed the expression as it’s required. Please perform an extensive test and feedback me at your convenience. Thnx.

LeventK · January 7, 2021, 11:25am

For anyone who is eager about the solution, here is the final expression I have set:

IF(
	DATE([TIME IN]) = DATE([TIME OUT]),
	IFS(
		AND(
			TIME([TIME OUT]) >= TIME("00:00:00"),
			TIME([TIME OUT]) <= TIME("06:00:00")
		),
		IF(
			TIME([TIME IN]) <= TIME("17:00:00"),
			TOTALHOURS(DATETIME(DATE([TIME IN])&" "&TIME("06:00:00")) - [TIME OUT])*1.00,
			(TOTALHOURS(DATETIME(DATE([TIME IN])&" "&TIME("06:00:00")) - [TIME OUT]) + TOTALHOURS([TIME IN] - DATETIME(DATE([TIME IN])&" "&TIME("17:00:00"))))*1.00
		),
		AND(
			TIME([TIME OUT]) > TIME("06:00:00"),
			TIME([TIME OUT]) <= TIME("17:00:00")
		),
		IF(
			TIME([TIME IN]) <= TIME("17:00:00"),
			0.00,
			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
)

Louwrens · January 7, 2021, 3:33pm

I must give you a major kudos for helping me @LeventK .
Thank you so much. It seems like its what I needed.

Hope you have a great day

LeventK · January 7, 2021, 4:01pm

You’re very welcome @Louwrens. My pleasure to be helped of. You can now remove my authorship both from the app and the gSheet.

Louwrens · January 8, 2021, 7:49am

Is seems like we have the final hurdle to jump.

Calculation is correct from PM - PM
Calculation is correct form PM - AM

We have a issue Calculating from AM - AM.

Please se below example: AM - AM

LeventK · January 8, 2021, 6:39pm

Louwrens:

Please se below example: AM - AM> >
3X_3_c_3c28ca927378e6ab8b84afacb8f8f317962deb02.jpeg548×299 8.77 KB

@Louwrens
This calculation shall in fact give zero (0), right?

LeventK · January 8, 2021, 6:42pm

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

Topic		Replies	Views
Need Assistance - Calculation Between Two DATETIME AppSheet Q&A expressions	4	12	July 15, 2021
Help Needed - Two DateTime Duration to exclude non working hours AppSheet Q&A expressions	17	43	March 17, 2024
The clock in after midnight AppSheet Q&A expressions	4	9	June 22, 2025

Overtime work based on Duration after 05 PM and Before am

AI Suggested topics