1. First Condition:

   • IF(AND(A1<TIME(6,0,0),B1<TIME(6,0,0)), 0, ...)
   • This checks if both the start and end times are before 06:00. If true, hours worked are 0.

2. Second Condition:

   • IF(AND(A1<TIME(18,0,0),B1<TIME(18,0,0)), (B1-A1)*24, ...)
   • This checks if both times are within the Day Shift (06:00 to 18:00). If true, calculates hours as total time difference multiplied by 24 to convert from days to hours.

3. Third Condition:

   • IF(AND(A1>=TIME(6,0,0),A1<TIME(18,0,0)), ...)
   • This checks if the start time is within the Day Shift. It calculates hours worked from start time to 18:00 and adds any time worked from 18:00 to the end time if it falls within the Night Shift.

4. Default Return: If none of the conditions match, the formula defaults to 0 hours.

Practical Example

Input Data

• A1: 17:00 (5 PM)
• B1: 19:00 (7 PM)

Calculation Steps

1. The start time (17:00) is within Day Shift.
2. The end time (19:00) transitions into Night Shift.
3. Formula calculates hours as follows:
   • From 17:00 to 18:00 = 1 hour (Day Shift)
   • From 18:00 to 19:00 = 1 hour (Night Shift)
4. Total hours worked = 1 + 1 = 2 hours.

Conclusion

This formula efficiently calculates the hours worked across day and night shifts while accommodating shifts that cross into different days. For further enhancement of Excel capabilities and advanced techniques, consider exploring the courses available on the Enterprise DNA Platform. This structured approach aids in understanding the application of Excel in practical scenarios.