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.
=(end_time-start_time)*24
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.
=INT((end_time-start_time)*24)
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.
=(end_time-start_time)*1440
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.
=(end_time-start_time)*86400
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
Conclusion
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.
Hi all
great article but I’m still struggling with one aspect of this and can’t make it work in one particular use case. For most of the time calculations I’ve tried it on, it’s perfect but when I try to find previous time, I can’t get the formula to comply.
For example, if I put 16:40-(5.5/24) to get 5.5 hours prior to the time on the left (16:40) – I correctly get 11:10. However, if I apply the same formula to the time of 04:05am [04:05-(5.5/24)] it gives me an error instead of the expected result of 22:35 (i.e. 5.5 hours earlier the day before).
Does anyone have any ideas on what I’m doing wrong? Thanks and much appreciative of any help provided.
Excel stores time as a decimal.
https://excelchamps.com/formulas/add-hours-into-time/
https://excelchamps.com/formulas/add-minutes-to-time/
https://excelchamps.com/formulas/separate-date-time/
With time, you are actually still dealing with dates as well. This means both 16:40 and 04:05 actually get stored as a serial number which ends up being 00/01/1900 16:40 and 00/01/1900 04:05 – you’ll see that if you update the cell format to dd/mm/yy hh:mm format (or other date format you might choose).
It’s just that Excel has sensed you entered a time so defaults to displaying just the time portion of the actual cell value – ie in hh:mm format.
Hence subtracting more then 4 hours and 5 mins from the time in the second example results in a date that is impossible for Excel, 1st Jan 1900 being the earliest date it works with.
..
One way round this could be to add 1 day to the time as it would not affect the time displayed *but* it would mean sorting time field would go weird.
..
Hope this help explain the issue.
……..
PS: The one thing I haven’t been able to explain to myself for a long time .. why does it handle day 0 of the first yr & mth it ‘can’ handle. I have no answer for that. Just knowing that the numeric (‘serial’) value “0” equates to a start date-time stamp means there’ll be a problem if I try to go negative.
Great explanation, Lindsay
THNK YOU!! I have been scouring the net trying to find out how to calculate two times as a number In Excel!!! You’re a lifesaver!
The above method has helped me no other that i know
Thanks
Thank you for your explanations, in 6. Calculate Difference as a Negative Value, you use two time dates. How would you do a negative if I want to do a negative dollar amount divided by a positive time amount.
eg: I trade the market and make $100 profit in 2.00 Hours = $50.00 P/Hour
I trade the market and lose $100 in 2.0 Hours = ##############
How can I fix this?
Do you know how to calculate the difference in a given start date and end date when you want to exempt weekends and certain holidays except not by using the NETWORKDAYS function?
Hi, I have a problem with calculating 2 times. Let´s say I ride my bike a total och 35h35m35s and the goal is 40h0m0s. To calculate the difference both negative and positive does´nt seem to work.
I tried the =TEXT(C42-C43,”hh:mm:ss”) where C42 is the total and C43 the set goal.
Excel does not recognize this as a formula at all and says it´s a problem with this formula?
I tried to search the net for a solution but did´nt find any.
PS. The format of the cell is the 37:30:55.
Regards!
Mika
Hello,
what if i need to calculate daily working hours (8) hrs if exceed 8 hrs to calculat the over time for the three shifts and the timing concidering timing from 19:00 to 07:00 as 1.5 rate and other hours will be in 1.25 rate
means
Example
Exmp1-08:00 to 20:00 8 hrs duty 4 hrs Over time ( 3hrs @1.25 & 1hr @1.5)
Exmp2-20:00 to 05:00 8 hrs duty 1 hr Over time (@1.5) (till 07:00 the rate will be 1.5)after 07:00 rate will be 1.25
Please advise
4/15/2021 14:44 4/17/2021 13:35 22:51
I am trying to calculate a time over the span of more than 2 days but it is giving results for less than one day
THNK YOU!! I have been scouring the net trying to find out how to calculate two times as a number In Excel!!! You’re a lifesaver!
Looking to use a 4 digit number as time (don’t want to input the colon) so times look like”
1347 2152 (start and end time)
What formats would you use as defined in each cell? General, Number or time (nn:nn)?
What formula would you use?
I would like to see the three cells like this:
nnnn nnnn nn:nn
Thanks.
Hello, how can I quickly calculate the time from a report that provideS the starting and ending time like this. To make sure you understand, the first line is 6.32.59 am to 6.50.24am
Start End
63259 65024
65024 84435
84435 90048
90048 102856
102856 104047
104047 111507
111507 113000
123449 132758
132758 135942
135942 141500
141500 144414
144414 145926
Hi Puneet,
Can you please help me with the formula needed to calculate the time difference in my example below? I keep getting the incorrect amount when the start and end time crosses days.
Start 09/20/2020 07:01:28 PM End 09/21/2020 08:12:36 AM
Within this time period, I also need to determine the hours worked between 7:00 pm – 7:00 am.
Thanks
Hi Puneet,
I have a slightly different problem and am hoping you can answer.
In my sheet as follows
Column C4= Start Date
Column D4= Start Time
Column E4 = Total break in imutes
Column F4= End Date
Column G4= End Time
and finally
Column H4= Total hours Work
I have tried different combinations from various websites, but am unable to find a solution for column H4.
Appreciate your help.
Thanks
How would I display time difference in hours and minutes instead of hours in whole numbers and decimals representing units of 100 instead of 60?
Eg: for 4 and half hours I want it to show 4.30 and not 4.5? I know the complex method is to convert it painstakingly and it is both lengthy and not the right way. Wanted to know if you have deduced a simpler and appropriate method. Thanks!
You are a life saver!!! Thanks! Keep up the good work!
Sir, 20th Feb, 2020
How could you do this ? Miracle !
I can’t believe.
Many, many efforts you might have taken. Isn’t it ?
Lot of thanks.
Interested in Excel, person like me , will very happy to note such Tips and Tricks.
I will be glad to receive more and notes from you in future too.
Once again thanks.
Kanhaiyalal Newaskar.
Hello,
How do I create a Nested IF function to calculate pieces picked between two times? Example: I have 42 cases picked between 8:30 am and 9:15 am. I need a formula to capture what was picked in each time period and have each quantity placed under the correct hour (ex: 8:00 am – 9:00 am and 9:00 am – 10:00 am). Is there a function that will allow me to do this? If so, please help.
Start Dates Trips END DATE Trips per day DAYS LEFT
22-06-2019 362 23-07-2019 9 8
22-06-2019 99 23-07-2019 10 8
05-07-2019 76 06-08-2019 12 26
23-06-2019 192 24-07-2019 14 7
I tried multiple formulas but as you see start date is 22 june and end date is 23 july the days left should show 1 day left is shows 8 can you help
Hi Puneet
How do we get a difference between a combination of date & time and date & time without using text to columns for separate date & time.
For this example :04/04/2019 06:00am – 05/04/2019 07:00pm
Thanks for this!
How could I modify the formula to check on the date?
for example, I started at 5 am yesterday, and and ended at 9 am today, the formula would just give 4 hours (if I multiply by 24) instead of 16 hours.
How do you calculate time difference when it’s negative, without getting the ####### treatment?
Reason I’ve needed this is for a work timesheet. If I work later than I should then I’ve worked longer & hence the difference will be positive (time worked in advance of being paid). I then keep a running total of this time.
But another day, when I leave early because I have excess time to “use up”, the difference is negative and ##### results, along with the running total becoming #######.
Potentially, the daily difference AND the running total could be negative so both need to display a negative value without the ### issue happening.
Only way I’ve found is to convert it to decimal number (as in your #2 example, but doing it into mins, then after that dividing that result by 60 to get back to hours as decimal). Problem is then it shows time as -1.25 for example.
How then can one convert the time difference to show as -1:15, as well as still be used as a number to keep the running total going?
How do I calculate time difference between two days i.e. 3/31/19 23:30 start time, and 4/1/19 02:30 end time? The result I want is 3.0 hrs?
DATE DAY IN TIME OUT TIME TOTAL
26-12-2018 Wednesday 11:30 7:45 PM 8:15
27-12-2018 Thursday 10:55 7:30 PM 8:35
28-12-2018 Friday 11:00 7:00 PM 8:00
29-12-2018 Saturday 10:45 6:21 PM 7:36
how can i calculate the total hours of four days, Each day wise i applied formula, kindly send the formula if any one knows
thanks for sharing knowledge.
You can also use datediff to get the time difference
I need to calculate Time with two different dates, Like TAT calculation