Timehseet issues.

[helenKG]

I'm having a nightmare. I have column D with a start time and column E with a finish time. I need column G to calculate if the employee worked during the hours of 20:00-06:00.

I need the amount of hours to show. I just cant figure it out.

Start Time Finish Time Total Hours Total Night Hours
10:00:00 22:00:00 12:00:00 08:00:00
0
08:30:00 22:00:00 13:30:00
0
0
0
0

[BlindAlley]

You will find everything you need in here using these formulas.

(Where 1/12 & 1/3 need to be changed to match different shift start / end times if required.)
Day Shift Formula: =IF(C2>B2,IF(MOD(C2+1/12,1)<=1/3,0,MIN(0.5833,C2)-IF(C2<=0.25,B2,MAX(B2,0.25))),MAX(0,0.5833-MAX(0.25,B2))+MAX(0,C2-0.25))*24
(Where 0.5833 and 0.25 are excel times for 2pm & 6am respectively, they may be changed to match different shift start / end times if required.)
Afternoon Shift Formula > =(B2>C2)*MEDIAN(0,C2-0.5833,0.5833)+MAX(0,MIN(1.20833,C2+(B2>C2))-MAX(0.5833,B2))*24
Night Shift Formula > =IF(C2<B2,(1+C2-B2)*24,(C2-B2)*24)-(D2+E2)

The worksheet will explain it far better than I could put words to.

[Richard Buttrey]

Hi

With Start time in A2, Finish time in B2

C2 Total Hours is:

Please Login or Register  to view this content.

D2 Night Hours:

Please Login or Register  to view this content.

[jhamm2018]

Removing post to comply with forum rules. Thank you for letting me know.

[AliGW]

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread