Calculate Total hours spanning midnight. Separate weekday, Saturday and Sunday hours - NO NIGHT SHIFT

[kayte83]

Hi, i am trying to calculate total weekday, Saturday and Sunday Hours. Each shift spans midnight. I can not find this anywhere, all similar posts include night shift. Night shift is irrelevant for me.

This is what I have so far, however I can't work out how to separate the hours after midnight.

Day	Date	Start	End	Weekday	Saturday	Sunday	Total
Saturday	09/03/22	16:00	8:00	0	16	0	16
Sunday	09/04/22	18:00	8:00	0	0	14	14
Monday	09/05/22	1:00	8:00	7	0	0	7
Tuesday	09/06/22	19:30	8:00	12.5	0	0	12.5
Wednesday	09/07/22	16:00	8:00	16	0	0	16
Thursday	09/08/22	20:00	8:00	12	0	0	12
Friday	09/09/22	21:00	8:00	11	0	0	11
Saturday	09/10/22	19:00	8:00	0	13	0	13
Sunday	09/11/22	17:30	8:00	0	0	14.5	14.5
Monday	09/12/22	16:00	8:00	16	0	0	16
Tuesday	09/13/22	20:00	8:00	12	0	0	12
Wednesday	09/14/22	20:15	8:00	11.75	0	0	11.75
	Total			98.25	29	28.5

E2 =MOD(End-Start,1)*24*(WEEKDAY(Date,2)<6)
F2 =MOD(End-Start,1)*24*(WEEKDAY(Date,2)=6)
G2 =(MOD(End-Start,1)*24*(WEEKDAY(Date,2)=7))


Below is what I would love to achieve with formulas.

Day	Date	Start	End	Weekday	Saturday	Sunday	Total
Saturday	09/03/22	16:00	8:00	0	8	8	16
Sunday	09/04/22	18:00	8:00	8	0	6	14
Monday	09/05/22	1:00	8:00	7	0	0	7
Tuesday	09/06/22	19:30	8:00	12.5	0	0	12.5
Wednesday	09/07/22	16:00	8:00	16	0	0	16
Thursday	09/08/22	20:00	8:00	12	0	0	12
Friday	09/09/22	21:00	8:00	3	8	0	11
Saturday	09/10/22	19:00	8:00	0	5	8	13
Sunday	09/11/22	17:30	8:00	8	0	6.5	14.5
Monday	09/12/22	16:00	8:00	16	0	0	16
Tuesday	09/13/22	20:00	8:00	12	0	0	12
Wednesday	09/14/22	20:15	8:00	11.75	0	0	11.75
	Total			98.25	29	28.5

Any help would be greatly appreciated. This has almost broken me.

 

[Tetra201]

See if the following formulas work for you:

=24*IF(D2>C2,(D2-C2)*(WEEKDAY(B2,2)<6),(1-C2)*(WEEKDAY(B2,2)<6)+D2*(WEEKDAY(B2+1,2)<6))
=24*IF(D2>C2,(D2-C2)*(WEEKDAY(B2,2)=6),(1-C2)*(WEEKDAY(B2,2)=6)+D2*(WEEKDAY(B2+1,2)=6))
=24*IF(D2>C2,(D2-C2)*(WEEKDAY(B2,2)=7),(1-C2)*(WEEKDAY(B2,2)=7)+D2*(WEEKDAY(B2+1,2)=7))

 

[kayte83]

This is Brilliant. Thank you.

Now I have been asked to add a public holiday column... Is it possible to do this with a "datevalue" formula? I have been playing around with it for too long now, I am thinking it may not even be possible?

 

[Tetra201]

See if the following formulas work for you:

Cell E2 (Weekday) =24*MOD(D2-C2,1)-SUM(F2:H2)
Cell F2 (Saturday) =24*IF(D2>C2,(D2-C2)*(WEEKDAY(B2,2)=6),(1-C2)*(WEEKDAY(B2,2)=6)+D2*(WEEKDAY(B2+1,2)=6))
Cell G2 (Sunday) =24*IF(D2>C2,(D2-C2)*(WEEKDAY(B2,2)=7),(1-C2)*(WEEKDAY(B2,2)=7)+D2*(WEEKDAY(B2+1,2)=7))
Cell H2 (Holiday) =24*IF(D2>C2,(D2-C2)*ISNUMBER(MATCH(B2,Holidays,0)),(1-C2)*ISNUMBER(MATCH(B2,Holidays,0))+D2*ISNUMBER(MATCH(B2+1,Holidays,0)))
where Holidays is a named range that contains holiday dates -- no Saturdays or Sundays.

 

[kayte83]

Thank you again for your reply. Unfortunately there are holidays on Saturdays and Sundays that need to be included.