Calculate total hours that fall between a specific date / time period (Over midnight)

[nik_re]

Good morning,

I would need a formula for the cell highlighted in yellow in the attached file.

Basically the operator work from 9.00 to 17.00.

I need to know the overtime hours in a specific time range in a month and calculate different totals per different time range.

I used this formula:

=(NETWORKDAYS(start,end)-1)*(upper-lower)
+IF(NETWORKDAYS(end,end),MEDIAN(MOD(end,1),upper,lower),upper)
-MEDIAN(NETWORKDAYS(start,start)*MOD(start,1),upper,lower)

and I would know if it can be readapted with upper and lower inverted as in my case (time range 22.00 to 6.00 and thus different from sort 00 - 23)

I know you can help me... pleaseee!!!

[nik_re]

I mean.... whatever formula will solve the column D6:D14 will be appreciated.

[Toonies]

hi try this in D6

Please Login or Register  to view this content.

its a Daddylongleg Formula


https://www.excelforum.com/excel-gen...t-and-end.html

[nik_re]

I have just tried and it seems to work.... Iīm in love DAY formula... wow
thank you so much . I am so happy.... donīt know how to explain.

Iīll try a bit this formula and then Iīll solve the thread.

Thank you Thank you Thank you )))))))))))))))))

[Toonies]

don't thank me thank Daddylonglegs

[nik_re]

I donīt know who is Daddylonglegs but I was not able to find any solution... you did it. Who is Daddylonglegs?

[Toonies]

they are a Forum Moderator on here and have much greater knowledge of Excel than I ever will

the Credit goes to them

[AliGW]

I donīt know who is Daddylonglegs but I was not able to find any solution... you did it. Who is Daddylonglegs?

Follow the link at the bottom of post #3.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.