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

[Arnaldo_Longart]

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 below):

• Time In
• Time out
• Hours worked
• Day Shift (6 am - 6 pm)
• Evening shift (6 pm - 12 am)
• Overnight (12 am - 6 am)

Thanks in advance,

Arnaldo

[#2]

@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:

1. Time In
2. Time Out
3. Hours Worked
4. Day Shift (6 am - 6 pm)
5. Evening Shift (6 pm - 12 am)
6. 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:

1. From midnight to the end of the shift:

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

1. 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.

[#3]

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  

[#4]

@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

1. 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.

[#5]

@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

[#6]

A file (without sensitive data) as well as information about Excel version, storage medium, etc. would also help.

[#7 · Accepted solution]

@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.

[#8]

Hi @SnowMan55

Thanks for the file with the correct formulas. It solved the situation; the worked hours are distributed correctly into each shift. 

Now, what do you suggest for me to comprehend the formulas you used? I would like to learn about it. My knowledge in MS Excel is limited.

Thanks again, really appreciated

Arnaldo 

[#9]

@Arnaldo_Longart 

This question is cross-posted on Chandoo

The total hours worked must be separated into "Day shift", "Evening Shift", and "Overnight Shift". |...

The solution I offered on that forum was

= LET(
    adjOut,      timeOut+(timeIn>timeOut),
    allocatedϑ,  MAP(timeIn, adjOut, LAMBDA(in,out,
      LAMBDA(
        SORT(HSTACK(out,shiftChanges),,,TRUE)
        - SORT(HSTACK(in,shiftChanges),,,TRUE)
      )
    )),
    firstNight,  MAP(allocatedϑ, LAMBDA(ϑ, INDEX(ϑ(),1,1))),
    firstDay,    MAP(allocatedϑ, LAMBDA(ϑ, INDEX(ϑ(),1,2))),
    evening,     MAP(allocatedϑ, LAMBDA(ϑ, INDEX(ϑ(),1,3))),
    secondNight, MAP(allocatedϑ, LAMBDA(ϑ, INDEX(ϑ(),1,4))),
    secondDay,   MAP(allocatedϑ, LAMBDA(ϑ, INDEX(ϑ(),1,5))),
    HSTACK(firstDay+secondDay, evening, firstNight+secondNight)
  )

Irrespective of that, since you have accepted a solution here, do not forget that you are obliged to report the fact on the other forum so that contributors do not spend time on a solved problem.

[#10]

@Arnaldo_Longart 

Start by reading the article on the LET function (I listed the URL in the _Info worksheet). You'll learn that it contains user-assigned names paired with cell references/calculations. The last argument-which is not paired-is the calculation that the function returns.

At its core, the last calculation in each LET function subtracts:

• the later (MAX) of the shift start time and the check in time
• from
• the earlier (MIN) of the shift end time and the check out time (the latter of which may have been adjusted up by one day).

The surrounding MAX function uses the larger of zero or the result of that subtraction. (If the times were entered incorrectly, the subtraction might create a negative number, which is not a valid time interval. This function replaces such a negative number with zero.)

The calculation for the overnight shift includes adding the result of an IF function because the employee may have checked in before midnight, and worked into the overnight shift (therefore, again, check in time is a later time than check out time). (Technically, that IF function should have subtracted shift_start from the result of the MIN function, but as shift_start in this case is midnight (=zero), it would be a wasted subtraction anyway.) Peter, Charles, Kate, Gary, Leonard, Paula, Thomas, and William are examples.

Similarly, the calculation for the "ordinary hours" includes adding the result of an IF function because the employee may have checked in before midnight, and worked all the way into the "ordinary hours" shift (therefore, check in time is a later time than check out time). Peter and Charles are examples.

So you can see, storing just times (rather than date-times) complicates the logic needed in the formulas. And if any employee were to work more than 24 hours straight, storing just the times means the formulas cannot detect and handle that situation correctly.

[#11]

Hi @SnowMan55 

Fantastic ! You are the best !

Arnaldo