Excel Formula that calculates hours worked between a set time

Hey Everyone,

I need to figure out a formula that calculates the total hours worked between 9pm and 7am in 1 formula.

A - Date
B - Start Time - 24 hour Time
C - End Time - 24 hour Time
D - Time worked between 9pm and 7am

Any insight on this matter would be appreciated.

Regards,

Try this:
shift_hours2_cy.JPG

Cell C7:

Please Login or Register  to view this content.

The following formula will show you how much time between your start and end time was between 9PM and 7AM.

Please Login or Register  to view this content.

It's not super elegant, but it does the trick. Hope that helps!

Originally Posted by cyiangou

Try this:
Attachment 415343

Cell C7:

Please Login or Register  to view this content.

Thanks, this would be great as long as I didn't need to have the values 21:00 and 07:00 showing in the sheet. These values would need to be integrated into the formula.

Oops! Missed that case. The formula below should take into consideration those cases.

Please Login or Register  to view this content.

Night hours 2.xlsx

Thanks, this would be great as long as I didn't need to have the values 21:00 and 07:00 showing in the sheet. These values would need to be integrated into the formula.

Seriously? It was an illustrative example.

More generally:

Please Login or Register  to view this content.

I was trying to show you how to do it, not do it for you.

Originally Posted by ThirdFret

Oops! Missed that case. The formula below should take into consideration those cases.

Please Login or Register  to view this content.

Attachment 415351

Awesome!, Thanks a lot. With this as a base I'm going to attempt beautification lol.

Regards

Originally Posted by cyiangou

More generally:

Please Login or Register  to view this content.

I was trying to show you how to do it, not do it for you.

My Comment was more directed at how I was going to move forward with the code, I appreciated what you gave me. Sorry if you misunderstood my intent.

Regards,