Excel functions to determine hours worked on day or night shift

[greergyec]

Hi All,


Hoping someone has an easy solution to decipher if hours worked by an individual fall into the day shift (0600 - 1800) or night shift (1800 - 0600).

The issue I'm having is that if someone has worked across both shifts, I need to be able to split these according to what shift they fit into.

Last Name	First Name	Date	Time Only (In)	Date Out	Time Only (Out)	Hours
YYYYY	XXXXX	07/05/2024	16:05:02	07/05/2024	04:30:55	5.22

This is the data that I'm working with, and I'm required to do it with multiple differing entries for hours all around the clock.

Any help is appreciated, I'm a novice!


Thank you!

 

[Kaper]

First things first: How could you receive 5.22 hours from above data?

 

[Kaper]

Assuming your data (and header) is in A1:F2, then in G2 (total hours)

=(E2+F2-C2-D2)

in J2 and K2 helper values (date and time shifted 6 hours) - these columns can be hidden

=C2+D2+6/24
=E2+F2+6/24

total on night shift and on day shift in H2 and I2:

=MAX(0,MIN(K2,INT(J2)+0.5)-J2)+MAX(0,MIN(K2,INT(J2)+1.5)-(INT(J2)+1))
=MAX(0,MIN(INT(J2)+1,K2)-MAX(J2,INT(J2)+0.5))+MAX(0,MIN(INT(J2)+2,K2)-(INT(J2)+1.5))

If you had office 365 you could not use helper columns and calculate directly using LET function instead, so:

=LET(CorInT,C2+D2+6/24,CorInD,INT(CorInT),CorOutT,E2+F2+6/24,MAX(0,MIN(CorOutT,CorInD+0.5)-CorInT)+MAX(0,MIN(CorOutT,CorInD+1.5)-(CorInD+1)))

and

=LET(CorInT,C2+D2+6/24,CorInD,INT(CorInT),CorOutT,E2+F2+6/24,MAX(0,MIN(CorInD+1,CorOutT)-MAX(CorInT,CorInD+0.5))+MAX(0,MIN(CorInD+2,CorOutT)-(CorInD+1.5)))

respectively.

If your data is located in different range, move it temporarily to A1:F2, insert the formulas and then move data and formulas to their standard locations

 

[greergyec]

First things first: How could you receive 5.22 hours from above data?

Thank you so much for the response! Sorry, it is an auto calculated from the timesheet, I had changed the dates to make a more challenging example and had not changed the total hours.

 

[greergyec]

Assuming your data (and header) is in A1:F2, then in G2 (total hours)

=(E2+F2-C2-D2)

in J2 and K2 helper values (date and time shifted 6 hours) - these columns can be hidden

=C2+D2+6/24
=E2+F2+6/24

total on night shift and on day shift in H2 and I2:

=MAX(0,MIN(K2,INT(J2)+0.5)-J2)+MAX(0,MIN(K2,INT(J2)+1.5)-(INT(J2)+1))
=MAX(0,MIN(INT(J2)+1,K2)-MAX(J2,INT(J2)+0.5))+MAX(0,MIN(INT(J2)+2,K2)-(INT(J2)+1.5))

If you had office 365 you could not use helper columns and calculate directly using LET function instead, so:

=LET(CorInT,C2+D2+6/24,CorInD,INT(CorInT),CorOutT,E2+F2+6/24,MAX(0,MIN(CorOutT,CorInD+0.5)-CorInT)+MAX(0,MIN(CorOutT,CorInD+1.5)-(CorInD+1)))

and

=LET(CorInT,C2+D2+6/24,CorInD,INT(CorInT),CorOutT,E2+F2+6/24,MAX(0,MIN(CorInD+1,CorOutT)-MAX(CorInT,CorInD+0.5))+MAX(0,MIN(CorInD+2,CorOutT)-(CorInD+1.5)))

respectively.

If your data is located in different range, move it temporarily to A1:F2, insert the formulas and then move data and formulas to their standard locations

Thank you so much for this - I really appreciate the assistance! For some reason I can't get the total hours formula to calculate correctly. I am terrible with excel so I do apologise. I've attached this mini sheet, could you please enlighten me if you get the opportunity?

 

[greergyec]

Book1
	A	B	C	D	E	F	G	H	I	J
1	Last Name	First Name	Date	Time Only (In)	Date Out	Time Only (Out)	Total Hours
2	YYYYY	XXXXX	01/04/2024	2:49:02	01/04/2024	15:00:41	1.133564815
3	YYYYY	XXXXX	01/04/2024	2:56:28	01/04/2024	13:16:01	0.983032408
4	YYYYY	XXXXX	01/04/2024	3:48:47	01/04/2024	3:49:07
5	YYYYY	XXXXX	01/04/2024	3:49:26	01/04/2024	16:57:08
6	YYYYY	XXXXX	01/04/2024	3:49:41	01/04/2024	16:57:04
7	YYYYY	XXXXX	01/04/2024	3:50:22	01/04/2024	16:25:15
8	YYYYY	XXXXX	01/04/2024	4:15:36	01/04/2024	17:09:30
9	YYYYY	XXXXX	01/04/2024	4:16:05	01/04/2024	18:09:02
10	YYYYY	XXXXX	01/04/2024	4:16:30	02/04/2024	4:12:56
11	YYYYY	XXXXX	01/04/2024	4:19:29	01/04/2024	16:22:52
12	YYYYY	XXXXX	01/04/2024	4:22:05	01/04/2024	16:47:52
Sheet1

Cell Formulas
Range	Formula
G2:G3	G2	=(E2+F2-C2-D2)

 

[greergyec]

I've figured it out now! Thank you so so much!

My date column still had the time included, so I removed this and now all of the formula's are working perfectly. Thank you so much you've saved me endless amounts of work

 

[Kaper]

Glad to hear so , I hope that after this correction of column E also other formulas (night and day shifts) work well.