Need help calculating Day/Night Shift Hours.

[jtedmonson42]

I've looked around and have tried other solutions I've seen posted for similar questions but none have worked completely.

This is a sample of the data.
Formula for day hours currently is:
=MAX(,MIN(D2,IF(B2<A2,1,)+B2)-MAX(A2,C2))

Night hours:
=E2-F2


It appears to work in all day shift start scenarios but the night shift starts that overlap into day time hours seem to be wrong for a lot of cases. Also, day and night shift start times are not static and vary based on location.

start time	end time	siteDayStart	SiteNightStart	shiftLength	dayHours	nightHours
8:30​	19:00​	4:30​	16:30​	10:30​	8:00​	2:30​
8:30​	19:00​	4:30​	16:30​	10:30​	8:00​	2:30​
19:30​	6:00​	4:30​	16:30​	10:30​	0:00​	10:30​
8:30​	19:00​	4:30​	16:30​	10:30​	8:00​	2:30​
22:30​	9:00​	4:30​	16:30​	10:30​	0:00​	10:30​
8:30​	19:00​	4:30​	16:30​	10:30​	8:00​	2:30​
18:30​	5:00​	4:30​	16:30​	10:30​	0:00​	10:30​

 

[ranman256]

always use full DATE/TIME . then it knows how to to the math.

 

[jtedmonson42]

That makes sense. Just trying to think of how to best approach it. These aren't intervals that have technically already happened it's simply start and end time on schedules.
So like for a night shift, I can put 1/1/2023 start and 1/2/2023 end, but for the day shift start and night shift start they technically start on the same day and making the date for those 1/1/2023 doesn't fix it.

 

[arthurbr]

general formula

=MOD(endtime-starttime,1)

Format as required

 

[jtedmonson42]

Think I was able to get it to work. Added a dummy date 1/1/2023 to all the start times and did a test if the shift is listed as night shift based on the schedule code to check if the end time was > the day shift start time + 1 day and then do the mod calculation and use the formula I was originally using.

dHours:
=IF(AND(Q2="Night",S2>AB2+1),MOD(S2-AB2+14,1),MAX(,MIN(AC2,IF(S2<R2,1,)+S2)-MAX(R2,AB2)))

nHours:
=MOD(AD2-AG2,1)

Thanks for the help steering me in the right direction.

start time

end time

metDay

1​

2​

3​

4​

5​

6​

7​

siteDayStart

SiteNightStart

shiftLength

dayHours

nightHours

dHours

nHours

1/1/23 8:30​

1/1/23 19:00​

6​

1​

1​

1​

0​

0​

1​

1​

1/1/23 4:30​

1/1/23 16:30​

10:30​

8:00​

2:30​

8:00​

2:30​

1/1/23 8:30​

1/1/23 19:00​

7​

1​

1​

1​

1​

0​

0​

1​

1/1/23 4:30​

1/1/23 16:30​

10:30​

8:00​

2:30​

8:00​

2:30​

1/1/23 19:30​

1/2/23 6:00​

7​

1​

1​

1​

1​

0​

0​

1​

1/1/23 4:30​

1/1/23 16:30​

10:30​

0:00​

10:30​

1:30​

9:00​

1/1/23 8:30​

1/1/23 19:00​

4​

0​

1​

1​

1​

1​

1​

0​

1/1/23 4:30​

1/1/23 16:30​

10:30​

8:00​

2:30​

8:00​

2:30​

1/1/23 22:30​

1/2/23 9:00​

1​

1​

0​

0​

1​

1​

1​

1​

1/1/23 4:30​

1/1/23 16:30​

10:30​

0:00​

10:30​

4:30​

6:00​

1/1/23 8:30​

1/1/23 19:00​

7​

1​

1​

1​

1​

0​

0​

1​

1/1/23 4:30​

1/1/23 16:30​

10:30​

8:00​

2:30​

8:00​

2:30​

1/1/23 18:30​

1/2/23 5:00​

3​

0​

0​

1​

1​

1​

1​

1​

1/1/23 4:30​

1/1/23 16:30​

10:30​

0:00​

10:30​

0:30​

10:00​