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!

Please share

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!

Powered by ConvertKit

Get Free Email Updates!

Signup now and receive an email once I publish new content.

I agree to have my personal information transfered to ConvertKit ( more information )

I will never give away, trade or sell your email address. You can unsubscribe at any time.

Abdelrahman Abdou
Latest posts by Abdelrahman Abdou (see all)
It's only fair to share...Share on Facebook
Facebook
0Pin on Pinterest
Pinterest
0Tweet about this on Twitter
Twitter
Share on LinkedIn
Linkedin