Excel Formula for Calculating Hours Worked in Shifts

This document will detail the process of calculating hours worked in two specific shifts in Excel: the daytime shift from 06:00 to 18:00 and the nighttime shift from 18:00 to 06:00.

1. Formula Description

To compute the total hours worked during these shifts, we will utilize the IF function, alongside the HOUR and MINUTE functions, in Excel. The logic hinges on determining the start and end times to categorize hours into the appropriate shift.

1.1 Formula Structure

=IF(AND(A1 >= TIME(6, 0, 0), A1 < TIME(18, 0, 0)), MIN(B1, TIME(18, 0, 0)) - A1, 
   IF(AND(A1 >= TIME(18, 0, 0), A1 < TIME(24, 0, 0)), MIN(B1, TIME(24, 0, 0)) - A1, 
   IF(AND(A1 < TIME(6, 0, 0), B1 >= TIME(6, 0, 0)), TIME(6, 0, 0) - A1, 
   0)))

1.2 Explanation of the Formula Components

• A1: This cell should contain the start time of the shift.
• B1: This cell should contain the end time of the shift.
• TIME(h, m, s): This function creates a time value based on the specified hours, minutes, and seconds.
• IF and AND: Used to check multiple conditions to categorize hours into the respective shifts.
• MIN Function: Ensures that no time past the defined shift limits is counted.

The formula checks:

1. If the start time (A1) is during the daytime shift (06:00 to 18:00).
2. If the start time is during the nighttime shift (18:00 to 06:00).
3. If the shift spans from nighttime into daytime.
4. Calculates hours accordingly, ensuring that any worked hours outside the limits are not counted.

2. Practical Example

2.1 Data Setup

Assume the following data inputs:

• Start Time (A1): 16:00
• End Time (B1): 19:00

2.2 Applying the Formula

Insert the formula into another cell, for instance, C1:

=IF(AND(A1 >= TIME(6, 0, 0), A1 < TIME(18, 0, 0)), MIN(B1, TIME(18, 0, 0)) - A1, 
   IF(AND(A1 >= TIME(18, 0, 0), A1 < TIME(24, 0, 0)), MIN(B1, TIME(24, 0, 0)) - A1, 
   IF(AND(A1 < TIME(6, 0, 0), B1 >= TIME(6, 0, 0)), TIME(6, 0, 0) - A1, 
   0)))

2.3 Output Interpretation

In this example:

• The worked hours from 16:00 to 18:00 will be calculated as 2 hours.
• The remaining hour from 18:00 to 19:00 will not be counted in the daytime shift.

Thus, the output in C1 will be 2:00 (2 hours worked).

3. Conclusion

The provided formula accurately calculates hours for specified shifts, allowing for effective time tracking within Excel. For further enhancements in data analysis skills, consider exploring courses on the Enterprise DNA Platform.