Excel Formula for Calculating Hours Worked in Shifts

Problem Statement

You need to calculate the hours worked in shifts that are defined in two segments:

• Day Shift: 06:00 to 18:00
• Night Shift: 18:00 to 06:00

You have the following variables:

• Start Time: Calculated as Start Time = D9 + C9
• Finish Time: Calculated as Finish Time = F9 + E9

Excel Formula

The following formula can be used to calculate the total hours worked for the specified shifts:

=IF(AND((D9+C9) < TIME(6,0,0), (F9+E9) < TIME(6,0,0)), 0, 
 IF((D9+C9) < TIME(6,0,0), ((F9+E9)-TIME(6,0,0))*24,
 IF((F9+E9) >= TIME(18,0,0), ((18/24)-(D9+C9))*24 + (F9+E9-(18/24))*24,
 IF((D9+C9) >= TIME(18,0,0) , (F9+E9-(D9+C9))*24, 
 (F9+E9-(D9+C9))*24))))
)

Explanation of the Formula

1. Checking Conditions:

   • The formula uses AND and IF functions to evaluate different scenarios based on the start and finish times.

2. Time Calculations:

   • It considers whether the shifts start before 06:00 or end after 18:00 to calculate the respective hours correctly. It also accounts for shifts that cross over midnight.

3. Return Values:

   • It returns the total hours worked based on the calculations, ensuring to convert the time difference to hours by multiplying by 24.

Practical Example

Assume the following:

• Start Date (D9): 2023-10-01
• Start Time (C9): 22:00 (10 PM)
• Finish Date (F9): 2023-10-02
• Finish Time (E9): 02:00 (2 AM)

Calculation Steps

1. Calculate Start Time:

   • Start Time = D9 + C9 = 2023-10-01 22:00

2. Calculate Finish Time:

   • Finish Time = F9 + E9 = 2023-10-02 02:00

Applying the Formula

Using the formula provided, the calculations occur as follows:

• Since the shift crosses midnight, the formula accounts for both the night shift and any hours worked during the earlier and later parts of the night.

Results: The output of the formula will yield the total hours worked between 22:00 and 02:00, correctly attributing hours before and after the midnight crossover.

Conclusion

This Excel formula provides a clear method to calculate hours worked across two shifts that may span multiple days. It is crucial to ensure that the proper date and time formats are utilized for accurate calculations. For further learning about Excel formulas and advanced data analysis techniques, consider exploring the resources available on the Enterprise DNA Platform.