Calculate night shift hours on 24 hr schedule

[megancoleman920]

I need to calculate night shift and day shift hours for a 24 hour schedule. Employees can clock in at any time during the day or night and often work more than 12 hour shifts (but not always). Night shift starts at midnight and ends at 8am. I need the formula to account for when the employee clocks in at 7pm and clocks out at 8:30am the following morning (for example), which would be 8 hours of night shift, 5.5 hours of day shift. Please and thank you in advance!!

[CK76]

Solution will depend on your data layout and what's stored in each field/cell.

I'd recommend uploading sample workbook (with desensitized info) that give enough examples along with your expected result (manually inputted).

To upload, use "Go Advanced" button and click on "Manage Attachments" hyperlink. It will launch new tab/window for managing file uploads.

[megancoleman920]

Thank you for the advice! A sample spreadsheet is attached. I'm looking for a formula to calculate night hours and day hours separately, if possible.

[KOKOSEK]

Try:

Please Login or Register  to view this content.

[Bo_Ry]

Please try at D2
=(B2>C2)+C2-B2)
set custom format to [h]:mm

Or this if you want hours with decimal
=((B2>C2)+C2-B2)*24

[CK76]

Assuming you have Bo_Ry's formula in D column (Total).

For Day shift (E2)

Please Login or Register  to view this content.

For Nightshift.

Please Login or Register  to view this content.

[megancoleman920]

Thank you!!!

[CK76]

Glad we could help. If this solved your issue, please mark the thread as solved by using Thread tools found at top of your initial post