A question that is often asked is “when I add up times and the total comes to more than 24 hours, why does it not show correctly?”. To answer this, I’m using a timesheet as an example:
**Download the spreadsheet here**
My working day is split into 2 sessions.
To calculate how many hours/minutes I worked in the first session, I subtract the time entered in column B from the time entered in column C.
To calculate how many hours/minutes I worked in the second session, I subtract the time entered in column D from the time entered in column E.
The formula in column F adds up the results of those two calculations to give me the total time worked on each day. For example, the formula in F3 is =(C3-B3)+(E3-D3) calculates the number of hours worked on Monday.
The formulas in F9 and F10 are identical. They are both =SUM(F3:F7). So why do they display different answers?
The key is the formatting.
F10 is formatted using the Custom format hh:mm
F9 is formatted using the Custom format [hh]:mm
When you enter a formula in a cell to add up times and the result of the formula is over 24 hours, Excel displays the difference between the result and 24 hours. In other words:
The total of F3:F7 is 33 hours, 15 minutes
33 hours, 15 minutes minus 24 hours is 9 hours 15 minutes
To display the result correctly
- Right click the cell containing the date
- Select the Custom category
- Select [hh]:mm from the list of formats
- If the [hh]:mm format is not listed, create a new format by typing into the box at the top of the list
**Download the spreadsheet here**
Brilliant! I’ve looked for this many times but never found the answer. Bookmarked the site for future reference.
Brilliant! This saved my hours of searching to do a time sheet.
How do y calculate if you started at say 11pm and finished at 6am? I can’t figure out how to do it!
Lesley – Check out this other tutorial I wrote https://theexceltrainer.co.uk/calculate-time-difference-where-period-exceeds-24-hours/
Thank you!
Wow literally years of trying to figure out why it didn’t work. 10 minutes of reading this and I figured it out. thank you so much.
You’re welcome!
Mike you are the man. You just saved the day!!!
Mike,
How do I take a cell formatted as [hh]:mm and subtract 40 hours from it? IE if a cell is 45:03, I want to return 5. Thx for being awesome!
Try this…
Put 45:03 in A1
Put this formula in another cell: =HOUR(A1-(40/24))
I was developing a spreadsheet for a new requirement to track monthly hours on radio nets. One of my beta testers came back with the problem once you exceeded 24 hours total… A quick Google search brought me to your procedure. I literally had the spreadsheet tallying cells properly reformatted, tested myself, checked into a net (multi-tasking), and the reply sent in 22 minutes. I’m sure the users will be impressed with the improved product.
Thanks so easy when you find it
I have the value 10211:09:12 in the format of HH:MM:SS. I need to calculate the average handing time by dividing a number. lets say 209. 10211:09:12/209 would be my calculation and result should be “48:53:97”. Please help in defining the excel formula for this.
Thanks a lot
I just created a new tutorial: https://theexceltrainer.co.uk/excels-10000-hour-limitation – this gives you the answer
thank you thank you thank you!!!
Hi,
I’m trying to sum up times, but my date format comes in like: “d/MM/yyyy hh/mm/ss” I’ve tried the same logic of formatting to “d/mm/yyyy [hh]:mm” but in the example given (using the same total nrs but in format 1/01/1900 6:30), when I sum I get “6/01/1900 12:15” in stead of “1/01/1900 33:15” – any idea anyone? Thank you!!
@Souad – if you have Excel 2013 or 2016 on Windows and just require the times, Flash Fill is the easiest way. If necessary, insert a blank column to the right of the dates/times and in the new column, on the row containing the first item, type just the time (e.g. 06:30) and then press CTRL+E. Now you have just the times and can format them as [hh:mm]
If your situation is different, please give some more details.
Mike
First class and simple to follow for an advanced user who keeps forgetting!!! Thanks
Is it possible to display time over 24 hour AND negative time? I seem to be able to do only one or the other. When using 1904 time, the [hh] formatting doesn’t seem to work for hours over 24.
@Jim – I did it this way…
Don’t set Excel to use 1904 time system.
Format A1:C1 as hh:mm – use custom format
A1: Type 50:00
B1: Type 10:00
C1: Type =IF(B1>=A1,(TEXT(B1-A1,”[h]:mm”)), CONCATENATE(“-“,TEXT(A1-B1, “[h]:mm”)))
Thanks. Pity MS help isn’t as clear and helpful!
I am trying to work out how long my engineers are onsite so say they start at 20:58 and finish at 04:45 I keep getting 16hrs 13 mins as an answer I cant find a formula that works
Hi Claire. Try this:
A1: 20:58
B1: 04:45
C1: =(B1-A1)+(A1>B1)
Explanation… time is represented as a fraction of a day. The comparison A1>B1 returns True (1) or False (0). If if true 1 day (24 hours) is added
Just wanted to add to the long list of “Thank yous”. All I needed was the [hh]:mm; your explanation and example were perfect.
You’re very welcome Tony
how about this..
Date Time-in Time-out Total
2 19:55 6:07 10:12
5 9:09 18:50 9:41
6 9:46 18:27 8:41
7 9:54 19:38 9:44
8 9:19 18:59 9:40
9 9:17 19:09 9:52
11 18:55 6:10 11:15
Total 4629:05
@Mike… assuming 19:55 is in B1 and 6:07 is in C1, try this in D1…
=(C1+(C1 < B1)-B1) Then copy it down Not sure where the 4629:05 comes from though
thank you!
many thanks
I have a excel timesheet that I need to add the total hours for each day. The problem is I need it to round (7 minute rule) each hour so that in the total it’s in 15 minute increments and not like the exact time that has to be shown in each cell. I hope this makes since.
@Ashley. Have a look at this tutorial – https://theexceltrainer.co.uk/rounding-time-to-the-nearest-15-minutes/
hi i need to change date when time exceeds 24 hours for example
C D
dd:mm:yyyy hh:mm:ss
1 04:01:2017 23:54:13
1 07:03:2012 12:51:16
when i add +5 hours to time using F1 = TEXT(D1+TIME(5,0,0),”hh:mm:ss”) it becomes
C D E F
dd:mm:yyyy hh:mm:ss dd:mm:yyyy hh:mm:ss
1 04:01:2017 23:54:13 04:01:2017 04:54:13
1 07:03:2012 12:51:16 07:03:2012 17:51:16
time changes but not date when above 24 hours
I need to change the date as well if only time goes above 24 hours (not all the time) like this
C D E F
dd:mm:yyyy hh:mm:ss dd:mm:yyyy hh:mm:ss
1 04:01:2017 23:54:13 05:01:2017 04:54:13
1 07:03:2012 12:51:16 07:03:2012 17:51:16
Appreciate any help
Hi mike, question if you dont mind, ive been trying to get this to work but have no luck, my issue is with calculating worked hours for a employee, but in my case the begin time is for instance 16:00 and the end time is 01:30 now if I input that i will get a negative value. Im on excel 2016 and here is a link to my excelsheet i made, its correct that theres a half hour difference, thats a deduction for eating diner. Any help is much appreciated
Hi mike, forgive me, just found a formula that works, i dont understand why but it does 😉
=(C5-B5+(C5<B5))*24-0,5 the whole formula reads abracadabra to me but hey, just as long as it works
thanks
btw, if you got a better more understandable formula i would love to hear/see it
I still had trouble with this after copying your supplied formulas and cell formating but I did find another workaround, by pure mistake I think. Not sure why yours did not work for me.
1st and 2nd column is time start and time finish, I formated cells as Number> Time> 13:30:50 (sixth selection from bottom of list).
Data entry for 1st and second column to be HH:MM:SS.
3rd Column to be cell formatted as Number> Custom> hh:mm (very bottom selection). Formula to be 2nd column cell minus 1st column cell (finish time – start time) = total hours worked, rounding every 60 minutes into 1 hour.
Total column for sum of all hours worked to be cell formatted to Number> Time> 37:30:55 (Third selection from bottom). The formula = sum of all cells desired to be totaled.
Not sure how I really found this out, but it works once tested. A little convoluted.
@Peter:
Try this in col E:
=IF(F1< D1,TEXT(DATE(RIGHT(C1,4),MID(C1,4,2),LEFT(C1,2))+1,"dd:mm:yyyy"),C1) It means if F1 is less than D1 (in other words if the time in F is earlier than the time in D), then generate a date from the content of C1 and add 1 to it. If F1 is not less than D1 then display the date from C1.
Hi Mike,
Apart from putting 2 sessions in a day, can we input an interval, lets say 1 hour for every session ( from time in – time out ).
I.E: total man hours tallied, subtract 1 hour (mandatory lunch break).
Just curious, Thanks
Hi Mike. I’m trying to find a way to add hours in excel for iPad. There doesn’t seem to be any way of entering a custom format in the same way that you have in your example. Any idea how I can do this please?
Hi Karen. I’ve just checked on my iPad and it’s not possible to set a custom format 🙁
I need to add up a list of daily hours worked and the total comes to over 100 hours. I don’t want to show it as days, just total hours. How do I format a custom setting to reflect hhh:mm:ss?
Hi Jason. Try [h]:mm:ss
hi mike, great stuff. can time be added and when the total goes over a certain number can the total be reset to start counting over? can’t figure it out
Hi Douglas. This can probably be achieved with some VBA code
Hello Mike
What if my work day is 8h/day and i want to sum to see what the total diff is at the end of the month or week.
I tried doing Total_day-8:00 for each day and this only work if the Total_day > 8.
I want to know at the end of the week if I worked less that my 40 hours or not.
@Mamo. Try this…
MON 08:00 07:00
TUE 08:00 07:30
WED 08:00 08:00
TOTAL Formulas here
Col A is days
Col B is Hours you should work
Col C is hours you actually worked
The formulas simply SUM the cells containing the times. One formula per column. Make sure you format the cells as [h]:mm
Hi Mike,
I have 3 hours need to do sum.
43:56:13
05:54:38
20:36:33
I need excel do auto sum on right below excel sheet and to do sum in columns.
Kindly teach me the right way. You are great. I very impressive.
@Mohd…Assuming the 3 times are in A1:C1, your formula will be =SUM(A1:C1) and that should do it. Worked for me!
Thanks very much. I’ve been screwing around with this for a half hour!!!
This [hh]:mm format solved my problem, thanks!
I was struggling with the “:ss” in my formula that keeps taking precious space, now I can have all numbers in a more efficient way.
I am having the most difficult time trying to come up with a spreadsheet that adds up time but in a 24 hour period!!! I have a time in and then time out but my spreadsheet includes the date on it and when I add the columns to give me hours worked it gives me a decimal I understand that excel works in decimals for time but I have tried changing the format to just [hh]:mm it will change the number to a huge decimal that doesn’t make sense. For example for 8 hours and 5 mins I get 194:00:00! Help!!!
Hi Jasmine. Try this…
A1: 13/10/2017 17:00:00
B1: 13/10/2017 18:00:00
C1: =MOD(B1,1)-MOD(A1,1)
Then format C1 with a custom format of hh:mm or whatever format you want
A1 10000:30
A2 5000:40
A3 =A1-A2 (iv written the formula as shown but this “#value!” appears…
@Marnel – it looks like you’ve hit the 10000 hour limitation. I wrote a post about it – https://theexceltrainer.co.uk/excels-10000-hour-limitation
Mike
I have [HH]:MM formula that adds up above 24hrs, great. However when I send this email on and its opened on a separate device, iPhone, ipad etc, the formula reverts back to the previous formatting i.e. 28:00 would show as 04:00 again. any ideas how to prevent it reformatting in transfer?
@Martin – I have tried it myself with my iPhone and get the same as you. It seems that this issue is only with the “preview” – when you tap on the name of the file in the email it opens it in preview mode. When I open the file in the Excel app on the iPhone it is OK
The only place where I found a solution for displaying duration over 24 hours in hours and minutes!
My problem solved , thank you
Thanks very much – sorted a problem out for me which will make a big difference.
Hi I am trying to just key enter hours but > than 24 hours. From this entry I am calculating payroll. If I key enter 25 hours it just goes back to zero. Please help!
Thank you so much! This was hard to fix without your help!
I have a spreadsheet with [h]:mm:ss as the format in each cell (representing how many hours, minutes, seconds I’ve worked). In the last cell in the row, I have the sum. Now I want to take that sum and subtract it from 60 hours. I have to stay under 60 hours. I want the answer to display in hours, minutes, seconds. I can’t figure out the formula for this subtraction. Thanks for your help.
using custom format is not working for me. I have to compile the run times for our transport team; I have tried hh:mm, h:mm, [hh]:mm], and [h]:mm the result is 9:15. If I use a time calucator it is 4:00, which is correct. A major difference and now I don’t trust the data I have to submit. The calculations on done on a day/month/ytd basis and if I have to do this manually it will be nightmare.
Below is the column of minutes for a particular day. I noticed the discrepancy because it was fairly obvious the number listed could not total 9:15
0:11
0:11
0:10
0:10
0:10
0:12
0:18
0:36
0:19
0:20
0:08
0:07
0:33
0:12
0:07
0:11
0:05
0:06
I have a row that is written in minutes. =TEXT(SUM(B12:AF12)/(24*60),”[h]:mm”)
then I have that column summed =TIME(SUM(HOUR(AG12:AG38)),SUM(MINUTE(AG12:AG38)),) with the formatting [h]:mm and it still not converting over 24 hours.
I have tried everything I can think of and done LOTS of googling!!