Getting 'normal' working hours and 'night differential' hours- PLEASE HELP

[tritri]

Hi! I want to find out the number of 'normal' working hours and number of 'night differential' hours in this given time.

Night shift starts at 10:00 PM - 6:00 AM, please see sample below.

# of normal working hours should be 4 hours and 15 mins while # of night diff hours should be 4 hours and 45 mins.

Format: h:mm

C2: 17:45
E2: 2:45


Thank you in advance!!

[David A Coop]

I can't see the formula you are using in C2 or E2, so can't be of much help.

HOWEVER - change your format to [h]:mm. It works a lot better when passing midnight.

I hope this helps, please let me know!

Regards,

David

If this has been helpful
- Please click on the *Add Reputation button at the bottom left.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

[tritri]

I tried this formula =MAX(0,MIN(E2,INT(C2)+1+"6:00")-MAX(C2,INT(C2)+"22:00")) which I saw on a previous post. Not allowed to post the link because I'm still a newbie. Anyway, the only problem I have is this formula is only applicable in date and time format while I only need time format. Also, theirs are computed in # of night differential hours only so I also need the number of 'normal' working hours.


C2: 17:45 (Start)
E2: 2:45 (Stop)
*These numbers are in h:mm format extracted from a biometrics device.

Hoping someone can help me with this. Thanks.

[tritri]

I tried this formula =MAX(0,MIN(E2,INT(C2)+1+"6:00")-MAX(C2,INT(C2)+"22:00")) which I saw on a previous post. Not allowed to post the link because I'm still a newbie. Anyway, the only problem I have is this formula is only applicable in date and time format while I only need time format. Also, theirs are computed in # of night differential hours only so I also need the number of 'normal' working hours.


C2: 17:45 (Start)
E2: 2:45 (Stop)
*These numbers are in h:mm format extracted from a biometrics device.

Hoping someone can help me with this. Thanks.

[David A Coop]

This formula can get extremely complex. It depends on how much the hours vary - before and after midnight etc.

Very simply, =IF(C2>=E2,(E2+1)-C2,E2-C2) will give you the elapsed time of 9 hours.

For the scenario you posted, =IF(C2<=TIME(22,0,0),TIME(22,0,0)-C2) will give you the 4:15 Normal hours.

This, however, is over simplified and probably won't work with lots of different times being entered. It will require multiple nested iff statements or helper columns. Sorry, but I am out of time to look further. at the moment.

Regards,

David