One of many features of Excel is that you can easily calculate the number of hours worked during a shift. Even though it is a really easy calculation most of the time (subtraction of the start time from the end time to get the total amount of hours worked), it can become tricky when calculating night shifts. For example, if I am trying to calculate the worked hours from 9:00 PM to 5:00 AM, a simple subtraction would return a negative number instead of 8 hours.
To allow Excel to calculate the hours worked for any work shift, we can use two methods.
Method 1: Enter a date and time
The first method is to not only enter a start/finish time but a start/finish date and time. That way, Excel is able to calculate the time between an end and finish time accurately (as it takes the date into account).
In the above example, we have inputted both time and date, and simply calculated the hours worked with a simple subtraction.
Method 2: The IF function
On the second method, we explain how to calculate the hours worked in case the inputs are only times and not (Date+Time). We use an IF function to analyze the two times before subtracting the Start time from the End time, because if the End time is smaller than the start time in case of an overnight shift, then the formula add 1 to the end time to correct the change of day
For example on Row 2, since 21:30 > 5:06, Excel adds 1 to 5:06 (which under a time format is 24:00). We therefore have 29:06 – 21:30 = 07:36.
Note: As we are playing with times and dates, that are values formatted in a certain way, formatting the results as a particular date or time is crucial to get the right information. Ensure that the Hours Worked cells are in a time format type HH:MM:SS (for example 08:34:18)
As alway, please leave a comment below if you have any queries/comment, we are always glad to read your feedback!
Bonus: Check out our Excel Date and Time Masterclass video on Youtube. It explains how date and time work in Excel + All Excel Date and Time functions.
You can also download our Excel Date and Time Cheat sheet through the below form!
Did you find this useful? Why not share the love and tell your friends on the different social media platforms.
Get our Excel Date & Time Functions Cheat sheet!
Enter your First Name & Email to get our Excel Date & Time functions cheat sheet and subscribe to our Newsletter!
- Learn all about the new XLOOKUP Function in Excel! - 2019-09-27
- FOR EACH Loop in Excel VBA - 2018-12-15