Tom’s Tutorials For Excel: Adding and Subtracting Time in Hours Minutes and Seconds
Formula examples for hours, minutes, and seconds being added or subtracted from time.
Hours
Example to add 3 hours: =$B$1+TIME(3,0,0)
Example to subtract 3 hours: =$B$1-TIME(3,0,0)
Minutes
Example to add 16 minutes: =$B$1+TIME(0,16,0)
Example to subtract 16 minutes: =$B$1-TIME(0,16,0)
Seconds
Example to add 48 seconds: =$B$1+TIME(0,0,48)
Example to subtract 48 seconds: =$B$1-TIME(0,0,48)
Combination of Hours, Minutes, and Seconds
Example to add 3 hours, 16 minutes, and 48 seconds: =$B$1+TIME(3,16,48)
Example to subtract 3 hours, 16 minutes, and 48 seconds: =$B$1-TIME(3,16,48)
When you first enter your formulas, the range will look odd, similar to this:
To fix that, select the range, press Alt+O+E and format the range as Time.
Thank you so much!
This was really helpful, wouldn’t have been able to figure this out myself
and most other sites are not mentioning ‘Time’.
Again, many thanks!
–
If the start time is for example 3AM and I want to subtract 5 hours the answer should be 10PM, but the formula =$B$3-TIME(5,0,0)doesn’t work, cell shows #######.
Any way to get this to work?
You’re right, I didn’t cover the midnight thing in that example when subtracting time.
Suppose as you say, you have 3:00 AM in cell B3.
You want to subtract 5 hours from that, to return 10:00 PM.
In some other cell, enter the formula
=MOD(B3-“5:00”,1)
I’m updating my website and will add the “midnight factor” in future formula examples.
How would I subtract a time total (29:41 mm/ss) generated by a formula from a total time of (45:00 mm/ss)?
Thanks!
Times are entered as hours, minutes, and seconds. In your question, there are no hours involved, but to get the answer, you’d want to enter the times in a cell whereby Excel is comfortable working with the times and then format the result to make sense when you see it visually.
Suppose you want to enter the 45 minutes total time in a cell, let’s say cell A1. You can do that by entering it like this:
0:45:00
The preceding 0 (using the colon character to separate it from the 45) serves as the placeholder for hours. If you had entered 45:00 then Excel would regard that as 45 hours and zero minutes. But to Excel, 0:45:00 means zero hours, 45 minutes, and zero seconds, which is what you want.
With 0:45:00 in cell A1, the same logic applies for entry of 29:41 into cell A2, meaning you’d enter 0:29:41 into cell A2.
In cell A3, enter the formula =A1-A2.
If in cell A3 you do not see the result of 0:15:19, then format the cell as [h]:mm:ss.
If you don’t want to see the preceding zero to represent no hours, then format cell A3 (or all 3 cells if you want in this case) as [mm]:ss and you will see the visual result of 15:19.
I need to find the difference between times over two consecutive days; e.g.
Day one: 23:02:10
Day two: 00:03:25
i.e. subtract Day one from Day two (the day info is not required)
Covering a few scenarios depending on how your data is arranged…
For times that cross midnight, example 17:00 to 02:00 then use the formula
=A2-A1+(A1>A2)
or
=C9+(C8>C9)-C8
or
=MOD(C9-C8,1)
or
=MOD(B1 – A1, 1)*1440
The 1440 represents minutes in a day.
Suppose you have 3:00 AM in cell B3. You want to subtract 5 hours from that, to return 10:00 PM.
In some other cell, enter the formula
=MOD(B3-“5:00”,1)
format result cell as [h]:mm
Thanks, Very helpful
I’m entering a data which in the format hrs.min (874.52 880.40), which are machine hours, how can we subtract them…
Interesting & Helpful.
Thanks.
I have done this but I am getting a #value
I know the formulas work, so assuming you followed the instructions as I wrote them, it could be a case of international syntax difference(s) or maybe the original data (the times in the cells you are calculating) are not really times but text. Please check your cells and formulas again, and if you still get an error, post back with
• what value you have in what cell
• the formula you are using
• how you formatted the cells
• what your expected result is
Something is happening on your worksheet that is different than what my pictures and descriptions are showing.
I need to set conditional formatting to compare time entered in a cell against the TIME in cell A1. I need any cell in column H to turn orange if the time entered in Column H is within 1 hour prior of cell A1. Can you assist? And cell A1 is formatted in a 24-hour clock….. =TEXT(NOW(), “HH:mm”)+TIME(5,0,0)
Replied by email.
Hi Tom,
Greetings from Scotland. I’m pulling my hair out trying to fix a gremlin in a spreadsheet for work. My Boss started constructing it to report on Standard hours worked, Overtime hours and Higher grade paid time. My issue is that the way it has been formatted, that it’s set up to show standard time as 35hours with any additional standard hours automatically populating a separate overtime column. All fine so far, however for some reason it always shows 35 standard hours (even when no daily hours are entered, which is a problem when that then adds into a total hours column). It wasn’t an issue until I tried to add in nightshift higher grade hours, as its now adding unworked daytime hours (the 35) to my total.
I’d be extremely grateful if you would be willing to assist me.
Using my picture as an example, you can see that neither regular nor overtime hours would be represented when there is a zero or no hours worked in column B, although in this picture all the people have hours worked but if any did not, no hours or pay would show.
The way I have my worksheet set up, which is probably different than yours but the concept is still the same, I just changed the number in cell B3 from 40 to 35 and everything calculates properly, with no “35” popping up for people who did not record any worked hours.
Here is another picture to more completely illustrate the point.
Hi,
Need one help,
I have planned working hours which is 324:00:00 (HH:MM:SS) and achieved hours 279:25:00 (HH:MM:SS), when i am using simple subtraction it returns nothing. Kindly help me out.
Thanks
Just now I entered this into cell A1
324:00:00
and this in cell A2
279:25:00
In cell A3 I have this “simple subtraction” as you call it:
=A1-A2
which returns
44:35:00
which looks correct to me.
So not only do I do what you said you do,
I got a returned value from my formula
and
the returned value is correct.
So unless you tell me more than what you told me so far, I cannot duplicate your problem.
HI Need one Help please
First_Enquiry_Date Resolved Date Total Time Solution Required After Excluding
11/03/2023 20:14 12/03/2023 16:19 20:04 I want to Exclude Time From night 10 PM to Morning 08 AM 10:04