Overtime work based on Duration after 05 PM and Before 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?

Is your START and END columns TIME or DATETIME type?

1 Like

Yes that’s Correct @LeventK

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

1 Like

Sorry I read to quick - Is DATETIME

1 Like

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

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?

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?

2 Likes

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 :face_with_monocle:?

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.

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

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

@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])))
		)
	)
)
2 Likes

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?

@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])))
		)
	)
)
1 Like

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

I am getting this error.

My [Time out] being start of work.

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

1 Like

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.

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

That is correct yes.