Overtime work based on Duration after 05 PM and Be...

Louwrens · 12-28-2020 01:57 AM

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

Is your START and END columns TIME or DATETIME type?

Louwrens

Yes that’s Correct @LeventK

LeventK

Correct for which one @Louwrens? TIME type OR DATETIME type?

Louwrens

Sorry I read to quick - Is DATETIME

LeventK

@Louwrens
TOTALHOURS([END] - [START]) - 11

Louwrens

Overtime is not always work from 5pm to 6M.

A job can start of @3PM and run to 9PM.

Only the hours between 5PM and 9PM should then be calculated as overtime

TOTALHOURS([END] - [START]) - 11

Is will just Minus 11 hours of anything that is logged correct?

LeventK

Gotcha. So something like this might work for your case:

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

Correct.

However, if the start and end dates are different, then you need to take another approach. Provided the users are not logging the start and end datetime for each particular day, then the calculation might be a bit of problem.

Are you considering 6am - 5pm (same day) as normal work time (11hrs per day incl. breaks??)
Are you considering 5pm - 6am (next day) as overtime work?

Louwrens

The concept or how the overtime is work out is as follows.

(We are a High Spez transport Company)

We pay overtime for anything after 5pm up to 6am
If a job starts 3pm the afternoon the overtime will only start calculating from 5pm and stop calcuting at 6 am even though the job is still running.

If a jobs starts at 11pm and finishes as 7 am the overtime needs to stop calculating at 6am. (Totaling 6hour overtime )

There is no break schedule in the system.
Employees work on a shift roaster.

The Expression provided is pushing out 11 hour if its one hour past 5 ?

Louwrens

Are you considering 6am - 5pm (same day) as normal work time ( 11hrs per day incl. breaks?? )
Are you considering 5pm - 6am (next day) as overtime work?

I thinks this somewhat in line but I am not sure what is meant with the same day and the next day.

LeventK

@Louwrens
I have possibly misunderstand your concept but clarified my own Q from your post above. Relying on your statemen in the initial post: “Furthermore an employee can start working on the 1st of the month and only end a few days later”, I believe we need another approach to the problem.

Louwrens

Great to know.
Do you possibly have any suggestions which I can try?

LeventK

@Louwrens
You can try with this, which will give you a total of normal working hours excluding any hours from 6am to 5pm.

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

Louwrens

Can’t wait to see if it works but, Number of opened and closed parentheses does not match.

What will be the easiest way to fault find this?

LeventK

@Louwrens
There was a redundant paranthesis and a comma was missing. Fixed them below:

IF(
	AND(
		TIME([START]) >= 6,
		TIME([END]) <= 17
	),
	TOTALHOURS([END] - [START])*IF(DATE([START]) = DATE([END]),1,ABS(DATE([START]) - DATE([END]))),
	IF(
		AND(
			TIME([START]) < 6,
			TIME([END]) < 17
		),
		TOTALHOURS([END] - DATETIME(DATE([START])&" "&TIME("06:00:00")))*IF(DATE([START]) = DATE([END]),1,ABS(DATE([START]) - DATE([END]))),
		IF(
			AND(
				TIME([START]) < 6,
				TIME([END]) > 17
			),
			TOTALHOURS(DATETIME(DATE([END])&" "&TIME("17:00:00")) - DATETIME(DATE([START])&" "&TIME("06:00:00")))*IF(DATE([START]) = DATE([END]),1,ABS(DATE([START]) - DATE([END]))),
			TOTALHOURS(DATETIME(DATE([END])&" "&TIME("17:00:00")) - [START])*IF(DATE([START]) = DATE([END]),1,ABS(DATE([START]) - DATE([END])))
		)
	)
)

Louwrens

Arithmetic expression ‘(TIME([Time out]) >= 6)’ does not have valid

I am getting this error.

My [Time out] being start of work.

LeventK

@Louwrens
It’s totally my stupid mistake between copy&pastes, so do apologize. Here it’s:

IF(
	AND(
		HOUR(TIME([START]) - "00:00:00") > 17,
		HOUR(TIME([END]) - "00:00:00") > 17
	),0,
	IF(
		AND(
			HOUR(TIME([START]) - "00:00:00") >= 6,
			HOUR(TIME([END]) - "00:00:00") <= 17
		),
		TOTALHOURS([END] - [START])*IF(DATE([START]) = DATE([END]),1,ABS(NUMBER(DATE([START]) - DATE([END])))),
		IF(
			AND(
				HOUR(TIME([START]) - "00:00:00") < 6,
				HOUR(TIME([END]) - "00:00:00") < 17
			),
			TOTALHOURS([END] - DATETIME(DATE([START])&" "&TIME("06:00:00")))*IF(DATE([START]) = DATE([END]),1,ABS(NUMBER(DATE([START]) - DATE([END])))),
			IF(
				AND(
					HOUR(TIME([START]) - "00:00:00") < 6,
					HOUR(TIME([END]) - "00:00:00") > 17
				),
				TOTALHOURS(DATETIME(DATE([END])&" "&TIME("17:00:00")) - DATETIME(DATE([START])&" "&TIME("06:00:00")))*IF(DATE([START]) = DATE([END]),1,ABS(NUMBER(DATE([START]) - DATE([END])))),
				TOTALHOURS(DATETIME(DATE([END])&" "&TIME("17:00:00")) - [START])*IF(DATE([START]) = DATE([END]),1,ABS(NUMBER(DATE([START]) - DATE([END]))))
			)
		)
	)
)

Though this, I believe we need to dig down a little bit deeper. I couldn’t quite get how you need to/would like to calculate if the start and end dates are different? For example:

Start: 12/28/2020 06:00:00
End: 12/29/2020 19:30:00

OR

Start: 12/28/2020 06:00:00
End: 12/30/2020 04:30:00

I haven’t tested my expression with some fake data, so I’m not sure about the calculation’s integrity. Please do post me provided you don’t get any desired result.

Louwrens

The start and end dates will be more like:

Start 12/29/2020 16:00:00
End 12/30/2020 02:30:00
Needs to calculate the hours between 17:00:00 and of the 29th to the 06:00:00 of the 30th,but in this case worked stopped at 02:30:00.
Total Hours work overtime will be 9 hours 30 Min

As the overtime is paid afterhours

Currently it seems like it is calculating the hours between 6 - 17 and not the hours from 17 - 6.

LeventK

@Louwrens
So as per your post, you want to explicitly calculate the overtime, am I understanding correctly?

Louwrens

That is correct yes.

LeventK

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

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

@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

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

@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

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

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

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

Good day @LeventK,

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

Thanks for the help.

LeventK

@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

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

LeventK

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

Louwrens

Thanks @LeventK

LeventK

@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

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

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

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

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

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

Louwrens

Nope, that calculation should be 2 hours because it is still in the period form 17:00 to 06:00.

Overtime work based on Duration after 05 PM and Before am