Forum Discussion

Copper Contributor

Jul 06, 2024

Hours worked to be distributed into day shift, evening shift, and overnight shift

Hi ! I need help please. I'm not sure how to efficiently distribute the hours worked by each worker into the different shift. The information in the spreadsheet is as follow (also see screenshot b...

excel

Formulas and Functions

SnowMan55

Jul 09, 2024

Arnaldo_Longart The following formulas seem to work for the three shifts:

=LET( time_in, $B2, time_out, $C2 + IF($C2 < time_in, 1, 0),
    shift_start, TIME(6,0,0), shift_end, TIME(18,0,0),
    MAX( 0, MIN(time_out, shift_end) - MAX(time_in, shift_start) )
    + IF( $C2 < time_in, MAX(0, $C2 - shift_start), 0 )
)
=LET( time_in, $B2, time_out, $C2 + IF($C2 < time_in, 1, 0),
    shift_start, TIME(18,0,0), shift_end, 1,
    MAX( 0, MIN(time_out, shift_end) - MAX(time_in, shift_start) )
)
=LET( time_in, $B2, time_out, $C2 + IF($C2 < time_in, 1, 0),
    shift_start, TIME(0,0,0), shift_end, TIME(6,0,0),
    MAX( 0, MIN(time_out, shift_end) - MAX(time_in, shift_start) )
    + IF( $C2 < time_in, MIN($C2, shift_end), 0 )
)

(Spaces are included for readability; they are not required.)

The attached workbook includes notes and more test data.

2024-07-08 AL distribute hours among shifts.xlsx21 KB

NikolinoDE

Gold Contributor

Arnaldo_Longart

To efficiently distribute hours worked into day shift, evening shift, and overnight shift in Excel, you can use a combination of formulas. Here's a step-by-step guide on how to achieve this:

Step 1: Set Up Your Spreadsheet

Ensure your spreadsheet has the following columns:

Time In
Time Out
Hours Worked
Day Shift (6 am - 6 pm)
Evening Shift (6 pm - 12 am)
Overnight Shift (12 am - 6 am)

Step 2: Calculate Total Hours Worked

Assuming Time In is in column A and Time Out is in column B:

=IF(B2<A2, (B2+1)-A2, B2-A2)

This formula accounts for the possibility that the time out might be after midnight.

Step 3: Calculate Hours for Each Shift

Day Shift

=MAX(0, MIN(B2, TIME(18,0,0)) - MAX(A2, TIME(6,0,0)))

Evening Shift

=MAX(0, MIN(B2, TIME(24,0,0)) - MAX(A2, TIME(18,0,0)))

Overnight Shift

To account for the overnight shift crossing midnight, you need two parts:

From midnight to the end of the shift:

=MAX(0, MIN(B2, TIME(6,0,0)) - TIME(0,0,0))

From the beginning of the shift to midnight:

=MAX(0, TIME(6,0,0) - MAX(A2, TIME(0,0,0)))

Combine these two parts into one formula:

=MAX(0, MIN(B2, TIME(6,0,0)) - TIME(0,0,0)) + MAX(0, TIME(6,0,0) - MAX(A2, TIME(0,0,0)))

Step 4: Implement Formulas in Your Spreadsheet

Assuming you start from row 2:

In column D (Hours Worked):

=IF(B2<A2, (B2+1)-A2, B2-A2)

In column E (Day Shift):

=MAX(0, MIN(B2, TIME(18,0,0)) - MAX(A2, TIME(6,0,0)))

In column F (Evening Shift):

=MAX(0, MIN(B2, TIME(24,0,0)) - MAX(A2, TIME(18,0,0)))

In column G (Overnight Shift):

=MAX(0, MIN(B2, TIME(6,0,0)) - TIME(0,0,0)) + MAX(0, TIME(6,0,0) - MAX(A2, TIME(0,0,0)))

Step 5: Check for Errors

Ensure that the formulas are correctly handling cases where the Time Out is past midnight or spans multiple shifts.

Example

Time In	Time Out	Hours Worked	Day Shift	Evening Shift	Overnight Shift
7:00	15:00	8.0	8.0	0.0	0.0
17:00	23:00	6.0	1.0	5.0	0.0
22:00	7:00	9.0	0.0	2.0	7.0

These formulas should help you accurately distribute the hours worked into the corresponding shifts. The text, steps and formulas was created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.

Arnaldo_Longart

Copper Contributor

Jul 07, 2024

Hi NikolinoDE,

Thanks for the quick reply.

I think you are in the right track, however as I entered the formulas there are still some issues (red font ones). For example, in the case of 8 hours, the formula assigned correctly the 8 hours to the "day shift", however also added 6 hours to the "overnight shift".

I tried to attached the ms excel spreadsheet but didn't allow me. I used the figures from the example you mentioned in your previous emal.

Time in	Time out	Hours worked	day shift (6 am - 6 pm)	evening shift (6 pm - 12 am)	overnight (12 am - 6 am)
7:00	15:00	8:00	8:00	0:00	6:00
17:00	23:00	6:00	1:00	0:00	6:00
22:00	7:00	9:00	0:00	0:00	6:00

Please, can you check it to see what would be the issue.

Once again thanks,

Arnaldo

NikolinoDE

Gold Contributor

Jul 07, 2024

Arnaldo_Longart

It looks like there may be some issues with the formulas provided, especially when it comes to correctly distributing the hours into the shifts. Let’s rework the formulas to ensure they correctly distribute the hours.

Step-by-Step Breakdown

Setup Columns:

A - Time In
B - Time Out
C - Hours Worked (total hours)
D - Day Shift (6 am - 6 pm)
E - Evening Shift (6 pm - 12 am)
F - Overnight Shift (12 am - 6 am)

Step 1: Calculate Total Hours Worked

Place the following formula in C2:

=IF(B2<A2, (B2+1)-A2, B2-A2)

This formula calculates the total hours worked, accounting for cases where the time out is after midnight.

Step 2: Calculate Hours for Each Shift

Now, let's define the formulas to accurately split the hours across the shifts.

Day Shift (6 am - 6 pm) Place this formula in D2:

=MAX(0, (MIN(B2, TIME(18,0,0)) - MAX(A2, TIME(6,0,0))) * 24)

Evening Shift (6 pm - 12 am) Place this formula in E2:

=MAX(0, (MIN(B2, TIME(24,0,0)) - MAX(A2, TIME(18,0,0))) * 24)

Overnight Shift (12 am - 6 am) This one needs to handle two parts as shifts can span across midnight.

First part: From midnight to end of shift:

=MAX(0, (MIN(B2, TIME(6,0,0)) - TIME(0,0,0)) * 24)

Second part: From start of shift to midnight:

=MAX(0, (TIME(6,0,0) - MAX(A2, TIME(0,0,0))) * 24)

Combine both parts into one formula in F2:

=MAX(0, (MIN(B2, TIME(6,0,0)) - TIME(0,0,0)) * 24) + MAX(0, (TIME(6,0,0) - MAX(A2, TIME(0,0,0))) * 24)

Step 3: Implement and Test

Using the above formulas, implement them in the corresponding cells.

Example:

Time In	Time Out	Hours Worked	Day Shift	Evening Shift	Overnight Shift
7:00	15:00	=IF(B2<A2, (B2+1)-A2, B2-A2)	=MAX(0, (MIN(B2, TIME(18,0,0)) - MAX(A2, TIME(6,0,0))) * 24)	=MAX(0, (MIN(B2, TIME(24,0,0)) - MAX(A2, TIME(18,0,0))) * 24)	=MAX(0, (MIN(B2, TIME(6,0,0)) - TIME(0,0,0)) * 24) + MAX(0, (TIME(6,0,0) - MAX(A2, TIME(0,0,0))) * 24)
17:00	23:00	=IF(B2<A2, (B2+1)-A2, B2-A2)	=MAX(0, (MIN(B2, TIME(18,0,0)) - MAX(A2, TIME(6,0,0))) * 24)	=MAX(0, (MIN(B2, TIME(24,0,0)) - MAX(A2, TIME(18,0,0))) * 24)	=MAX(0, (MIN(B2, TIME(6,0,0)) - TIME(0,0,0)) * 24) + MAX(0, (TIME(6,0,0) - MAX(A2, TIME(0,0,0))) * 24)
22:00	7:00	=IF(B2<A2, (B2+1)-A2, B2-A2)	=MAX(0, (MIN(B2, TIME(18,0,0)) - MAX(A2, TIME(6,0,0))) * 24)	=MAX(0, (MIN(B2, TIME(24,0,0)) - MAX(A2, TIME(18,0,0))) * 24)	=MAX(0, (MIN(B2, TIME(6,0,0)) - TIME(0,0,0)) * 24) + MAX(0, (TIME(6,0,0) - MAX(A2, TIME(0,0,0))) * 24)

Verification

Make sure to validate the calculated hours for each shift with different scenarios to ensure they align correctly:

Shift starting and ending within the same day.
Shift crossing over from one day to the next.
Shift spanning across multiple shifts (e.g., starting in the evening and ending in the morning).

These formulas should correctly distribute the hours worked into the respective shifts without overlapping.

Arnaldo_Longart
Copper Contributor
Jul 07, 2024
NikolinoDE
Hi,
Thanks, but it is not working.
Here is the screenshot of my spreadsheet after implementing the updates in the formulas.

Please let me know if you any other idea to solve it.

Thanks, Nikolino
Arnaldo
- NikolinoDE
  Gold Contributor
  Jul 08, 2024
  A file (without sensitive data) as well as information about Excel version, storage medium, etc. would also help.

Forum Discussion

Hours worked to be distributed into day shift, evening shift, and overnight shift

Share

Resources