How to Calculate Time Difference in Excel

If you need to keep track of time entries, whether for a timesheet or some other purposes, it’s important you know how to calculate time differences in Excel, and that’s what I’ll show you how to do in this post. If you’re just looking for the difference in dates, including months, days, or years, then refer to this post. But if you need time differences, keep reading.

Entering time correctly

In order for the difference in time to be calculated correctly, it should ideally be entered in the right format to start with. Let’s use the example calculating the hours worked for a regular 9-to-5 shift.

You can use a 24-hour clock or AM/PM to enter time. If you’re using AM/PM, then you’ll want to make sure you enter a space between the time and the AM/PM indicator. For example: 9:00 AM rather than 9:00AM.

If you enter it correctly, then the value you just entered should align to the right. This means that Excel has interpreted it as time. You can also tell because in Excel it will show as 9:00:00 AM in the cell details. Normally, you aren’t going to enter seconds but Excel will track those extra zeroes anyway.

If you were to just enter 9AM then you’ll notice after hitting enter that nothing happens and Excel doesn’t align it to the right side of the cell. This means that what you’ve entered Excel is reading as text.

That is a problem because if you want to calculate the time difference correctly, it’ll get a whole lot more complex and require using IF and MID functions. It’s not impossible (and I’ll show you how to do so later on), but it will make it a lot more complicated.

Calculating the time difference

Now that you’ve made sure that the time entries are entered correctly, it’s time to show you how to calculate time difference in Excel. Here’s how it looks if I just take the end time and subtract the start time:

If you’re using the 24-hour clock then it looks okay. However, the calculations are still reading in the time format and while that may work okay for the 24-hour clock, it’s going to cause an issue for the AM/PM. So let’s change the format using the comma style so it doesn’t read like a date. Then, the data looks as follows:

This might seem even more confusing until you realize what Excel is doing. It’s assigning a value between 0 and 1 for the time of day. The value of 0.33 indicates that the time is one-third of the day, which is what an eight-hour shift would be since 8/24 is the same as 1/3.

However, using 0.33 isn’t going to be too helpful if you need hours to be able to track how much someone should earn for that shift. To solve this, simply multiply the time difference by 24. Then, your time difference looks as follows:

Now you see the eight hours that you may have first expected to see when calculating the difference between these times. Since it’s now reading as a number, you can multiply this by an hourly rate and arrive at the pay that is owing for the shift.

If you’re not looking for hours and just want the total minutes, then all you need to do is just multiply by a factor of 60 to convert the eight hours into 480 minutes.

Calculating hours worked when someone works the night shift

The above calculations work well if someone is working within the same day, but if someone starts at 9:00 PM and ends at 5:00 AM the next day, it’s not going to calculate properly if you simply take the difference between the numbers. In fact, the number would come out negative:

That’s obviously not what you’ll want. To account for this, you’ll need to add an IF statement into our formula. The IF function should look at whether the end time is earlier than the start time. If it is, you’ll want to add 1 to the calculation. Here’s how the formula for the difference calculation would look like:

I include the 24 in the calculation so that I don’t need to use an extra cell to convert the difference into hours. All the formula is doing is looking at if the end time is before the start time, and if it is, add a 1 to the end time before subtracting the start time from it. This method will work whether you’re using a 24-hour clock or the AM/PM format:

This method will work even if someone works a 16-hour shift:

Knowing how to calculate time difference in Excel isn’t difficult, and the key points to remember are as follows:

1. Ensure the time is entered correctly, and
2. Multiply the difference between the times by a factor of 24 for hours and another factor of 60 if you only want minutes.

Calculating time difference in Excel when the data is in text

If you need to know how to calculate time difference in Excel and you don’t have the luxury of the data being in the right format, the good news is it’s still possible to do so.

For instance, what if you want to enter the entire start and end times within one cell. In that case, you’re always going to be running into this situation. Suppose someone enters the following value into a cell: 9:00 AM-5:00 PM.

The format would be correct if there weren’t multiple times in that cell, which ensures it will read as text. That’s where you’ll need to use some data manipulation that involves using the MID function.

Let’s start by pulling the start time. For this, we’ll grab the numbers that come before “AM” but because it doesn’t matter whether it’s AM or PM, we’ll search for the letter “M” on its own:

=MID(A1,1,FIND(“M”,A1,1))

Here’s a breakdown of the formula:

• Argument 1: A1 is the cell we’re looking at.
• Argument 2: Start pulling from the first character.
• Argument 3: The length of the text goes up until the letter “M” is found.

The formula will yield a result of 9:00 AM. However, you’ll still need to convert this into a number and you can do this by multiplying the result by 1 or putting it inside the TIMEVALUE formula:

=TIMEVALUE(MID(A1,1,FIND(“M”,A1,1)))

This will now give us a value of 0.375 and it can be used for the calculation. Next up, we’ll calculate the ending time. To do this, we’ll again use the MID function but the key is to start pulling the data after the dash (-) sign:

=MID(A1,FIND(“-“,A1,1)+1,LEN(A1))

This one is a bit more complicated, but let’s look at the different arguments again:

• Argument 1: Still looking at cell A1.
• Argument 2: Use the FIND function to get to the position where the dash (-) is at and then add a 1 onto that to ensure we’re starting from where the number begins and not the dash (-).
• Argument 3: Even though you don’t need the total length of the string, you can use the LEN function to ensure the formula get everything that comes after the dash (-).

Doing this will give us a result of 5:00 PM and by again adding the TIMEVALUE function into it, it will give us a result of 0.7083

=TIMEVALUE(MID(A1,FIND(“-“,A1,1)+1,LEN(A1)))

You can now combine these formulas into one to do the calculation with text:

=TIMEVALUE(MID(A1,FIND(“-“,A1,1)+1,LEN(A1)))-TIMEVALUE(MID(A1,1,FIND(“M”,A1,1)))

This will result in 0.33 for the time difference, which is the correct result. However, with shifts that stretch into the following day, you’ll again run into the issue of how to deal with calculating time. If you don’t have night shifts to worry about, you can stop here.

But if you need to factor them in, then the easiest way to do so is using the AND and IF functions. We’ll want to look at whether the last two characters end in “AM” while also looking at whether “PM” is in the text:

=IF(AND(RIGHT(A1,2)=”AM”,(NOT(ISERROR(FIND(“PM”,A1,1))))),1,0)

Here’s a breakdown of the formula:

• The first formula inside the AND function looks at the last two characters in the text to see if they equal “AM”
• The second function looks for the characters “PM” anywhere in the cell. If it’s not found it will result in an error, that is why the formula begins with NOT and ISERRROR; you’ll want to add a 1 if it isn’t causing an error and “PM” is found.
• If both of the above conditions are met, a value of 1 will be returned. Otherwise it will result in 0. This can now be added to the earlier time calculation formula, added to the end time.

Here’s how the complete formula will look like after adjusting the end time in case there’s a cross over from PM into AM:

=IF(AND(RIGHT(A1,2)=”AM”,(NOT(ISERROR(FIND(“PM”,A1,1))))),1,0)+TIMEVALUE(MID(A1,FIND(“-“,A1,1)+1,LEN(A1)))-TIMEVALUE(MID(A1,1,FIND(“M”,A1,1)))

It’s a messy formula but it is only adding the 1 to the end time if it’s a PM start time and ends in the AM. The only thing left would be to multiply this all by 24 to get the entire shift total in hours:

=24*(IF(AND(RIGHT(A1,2)=”AM”,(NOT(ISERROR(FIND(“PM”,A1,1))))),1,0)+TIMEVALUE(MID(A1,FIND(“-“,A1,1)+1,LEN(A1)))-TIMEVALUE(MID(A1,1,FIND(“M”,A1,1))))

The key thing to note is for this formula to work it’s important to leave a space between AM and PM. For example, if I were to just enter 9:00AM, Excel wouldn’t read that properly and it would result in an error. By leaving a space, it helps with parsing the data out, otherwise the formula would need to be even more complex. If it isn’t in that format, it’s preferable to first clean up the data as opposed to building a very complicated, nested formula using IFs and MIDs.

The above example goes over a few scenarios and obviously you can adjust the MID and other functions as needed based on how your inputs look. Pulling date calculations out from text is possible, it’s just no very pretty.

If you liked this post on how to calculate time difference in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.