Calculating Day and Night Hours Worked in Excel

To calculate the number of day hours and night hours worked based on clock in and clock out times, we can use the following approach using Excel formulas.

Definitions

1. Day Hours: Typically, hours worked between a defined range (e.g., 6:00 AM to 6:00 PM).
2. Night Hours: Hours worked outside of the defined day range (e.g., 6:00 PM to 6:00 AM).

Required Inputs

Assuming the following columns in your Excel sheet:

• A: Clock In (e.g., 01/01/2023 6:00 AM)
• B: Clock Out (e.g., 01/01/2023 10:00 PM)

Excel Formulas to Calculate Day and Night Hours

1. Calculate Total Hours Worked

You first need the total hours worked, which can be calculated using:

=(B2 - A2) * 24

This formula finds the difference between clock out and clock in, multiplies by 24 to convert from Excel's date-time format to hours.

2. Calculate Day Hours Worked

To calculate day hours (between 6:00 AM and 6:00 PM):

=MAX(0, MIN(B2, DATE(YEAR(A2), MONTH(A2), DAY(A2)) + TIME(18, 0, 0)) - MAX(A2, DATE(YEAR(A2), MONTH(A2), DAY(A2)) + TIME(6, 0, 0))) * 24

3. Calculate Night Hours Worked

To calculate night hours (before 6:00 AM and after 6:00 PM):

=MAX(0, (MIN(B2, DATE(YEAR(A2), MONTH(A2), DAY(A2)) + TIME(6, 0, 0)) - A2) * 24) + MAX(0, (B2 - MAX(A2, DATE(YEAR(A2), MONTH(A2), DAY(A2)) + TIME(18, 0, 0))) * 24)

Explanation of the Formulas

Total Hours Formula

• B2 - A2 calculates the difference in time between Clock Out and Clock In.
• Multiplying by 24 converts that difference from days to hours.

Day Hours Formula

• MIN(B2, DATE(YEAR(A2), MONTH(A2), DAY(A2)) + TIME(18, 0, 0)) finds the earlier time of Clock Out or 6:00 PM (6 PM).
• MAX(A2, DATE(YEAR(A2), MONTH(A2), DAY(A2)) + TIME(6, 0, 0)) finds the later time of Clock In or 6:00 AM (6 AM).
• The formula calculates the overlap of these two times, which gives the number of day hours worked.

Night Hours Formula

• The first part MIN(B2, DATE(YEAR(A2), MONTH(A2), DAY(A2)) + TIME(6, 0, 0)) - A2 calculates the hours worked before 6:00 AM.
• The second part (B2 - MAX(A2, DATE(YEAR(A2), MONTH(A2), DAY(A2)) + TIME(18, 0, 0))) calculates the hours worked after 6:00 PM.
• Both parts compute the total night hours.

Practical Example

Assume the following data in Excel:

Step-by-Step Calculation

1. Total Hours:

   • Formula: =(B2 - A2) * 24 Result: 16 hours

2. Day Hours:

   • Formula: =MAX(0, MIN(B2, DATE(YEAR(A2), MONTH(A2), DAY(A2)) + TIME(18, 0, 0)) - MAX(A2, DATE(YEAR(A2), MONTH(A2), DAY(A2)) + TIME(6, 0, 0))) * 24 Result: 12 hours (from 6 AM to 6 PM)

3. Night Hours:

   • Formula: =MAX(0, (MIN(B2, DATE(YEAR(A2), MONTH(A2), DAY(A2)) + TIME(6, 0, 0)) - A2) * 24) + MAX(0, (B2 - MAX(A2, DATE(YEAR(A2), MONTH(A2), DAY(A2)) + TIME(18, 0, 0))) * 24) Result: 4 hours (from 6 PM to 10 PM)

Conclusion

The provided formulas effectively calculate the day and night hours worked based on clock in and clock out times. This methodology can easily be adapted for different ranges of day and night work hours as per organizational needs. For further improvements in your data analysis skills, consider exploring the Enterprise DNA platform.