If you work with data where you have time values then the tips which I’m going to share today can be helpful for you. Now listen.
In Excel, there are a lot of functions to deal with date and time. But there is no specific function that you can use to get the difference between times values.
As you know, a time value is a numeric value that is formatted as time but when you deduct one value from another it returns a decimal number and you need to format it again as time.
But here comes the pain:
When you format that result value it shows those values as hours and minutes (starting from 00:00) instead of showing the actual difference in hours and minutes.
So if you have a difference of 5 hours and 30 minutes it will show it as 05:00 AM. You don’t have to worry anymore.
1. Get the Difference in Hours and Minutes
This is one of the most common ways to get the difference between two times. Let’s suppose you have a start and end time (like below) where you need to get the difference between both.
- First of all, you can simply deduct the end time from the start time and you’ll get something like below.
- Now next you need to convert this time into hours and minutes.
- For this, select the cell and open format options (Ctrl + 1).
- After that, select the “Time” category and select the second time format.
- In the end, click OK.
Once you do this it will convert (Format) that time difference into hours and minutes instead of the normal time format.
2. Get Different in Hours in Numbers
This is not a common way to get a time difference but still useful and important. Let’s say you want to get the difference between start and end time in hours but not in normal numbers.
For example, if the difference between the start and end time is 4 hours and 30 minutes it should show it as 4.5 hours. Here’s the formula which you need to use.
Once you enter this formula it returns the hours in numbers and minutes (if you have them) in decimals.
How this Formula Works
As I said before, Excel stores date and time in numbers, and when you deduct the end time from the start time it returns a decimal. And when further you multiply it by 24 (the total number of hours in a day) it converts that decimal into the number of hours and minutes.
3. Get Different in Completed Hours
You can also calculate the completed hours between two times with this simple formula.
How this Formula Works
This formula is just like the formula which we have used in the second method, the only difference is here we have wrapped it in INT.
INT function returns the integer part decimal by rounding it down. You can learn more about it here. So when you enter this formula, first of all, it returns a decimal number for the hours, and then INT removes the fractional part and gives you completed hours.
4. Get Difference in Minutes
It happens sometimes that you need to track the time difference in minutes instead of hours, and in this case, the best way is to use the below formula.
In this formula, after deducting the start time from the end time we have multiplied it by 1440 which is the total number of minutes we have in 24 hours. And when you enter this formula you’ll get the difference between two values in minutes.
Quick Tip: If somehow you have seconds in your time values then you use the below formula to get the total complete minutes between start and end time.
5. Get Difference in Seconds
Same as minutes we can also get the difference in seconds. All you need to do is to use the following formula.
Here 86400 is the total second in 24 hours and when you enter this formula it will return the difference in start and end time in seconds.
6. Calculate the Difference as a Negative Value
In all the above examples, we have the end time which is greater than (later than) the start time.
Let’s suppose you have two time values where the end time is earlier than the start time. In simple words, if the start time is 11:00 PM on day 1, and the end time is 5:00 AM on day 2.
The end time is actually less than the start time and the simple subtract formula will not work and return a negative value which will cause Excel to display a string of hash characters (i.e. ########).
Don’t worry you can use a better formula for this. So the formula will be:
=IF(end>start, end-start, 1-start+end)
How this Formula Works
In this formula, you have a condition if the end time is greater than the start time then it will simply deduct the end time from the start time otherwise, firstly it will deduct the start time from 1 and then add it to the end time.
By subtracting the start time from 1, you will get the total time for day 1 and when you add it to the end time, you’ll get the total time of day 1 and day 2 which is the total difference.
Get the Excel File
For Excel users who deal with time data, it’s really important to know various methods to get the difference between two times.
And all the methods which we have used above can be used in different situations and I’m sure you have found all of these helpful.
But you need to tell me one thing.
Do you know any other method which you can use to get the difference between the start time and end time?
Share your views with me in the comment section, I’d love to hear from you, and make sure to share this tip with your friends.