Night Differential OT Formula 10:00pm to 6:00am

[bryman29]

Hi everyone! I'm working on a time-tracking project in Excel and need help with formulas. I have Column A (Start Time) and Column B (End Time) in 24-hour format. I need formulas for the following:

NDOT: calculate the time from 10:00pm to 6:00am frame range according to their actual time in and out schedule

 

[AliGW]

Not one of those pairs spans midnight!!!

But if they did, this would be a belt and braces approach:

=IF(--B2<--A2,(1-MAX(22/24,--A2-INT(--A2)))+MIN(6/24,--B2-INT(--B2)),0)

 

[vletm]

bryman29​

There seems to be some of those - eg the last row.
But
Do You expect daily results or shift results?
You gave only one rule - but there are more rules, which could change results.

 

[bryman29]

Still not calculated..

 

[AliGW]

Still not calculated.

Is that it? No 'thank you'? OK, well no more help from me - sorry. A lack of common courtesy is a HUGE turn-off.

 

[bryman29]

Thank you for your help. but it's not work in the template..

 

[vletm]

bryman29​

Do You still need something to solve Your case?

 

[bryman29]

bryman29​

Do You still need something to solve Your case?

Yes thank you for wanting to help me. I edited my template and input the full worked hour and I just want to know what can be the formula if only calculated their night differential overtime hour started at 10:00pm until 6:00am .

 

[AliGW]

This:

=IF(--B246-INT(B246)<--A246-INT(A246),(1-MAX(22/24,--A246-INT(--A246)))+MIN(6/24,--B246-INT(--B246)),0)

If you have Excel 2021 or newer:

=LET(a,--A246-INT(A246),b,--B246-INT(B246),IF(b<a,(1-MAX(22/24,a))+MIN(6/24,b),0))

 

[vletm]

bryman29​

Did You notice my #3 reply?
Previous time, while I solving ... this kind ...
there were much more rules to take care.
I should know clear rules, before I'll start to do something.
Of course, those Your used rules could be ... so different ... any others.