How to some times greater than 24 hours in Excel 2007
To sum times you usually use Excel built-in function SUM. If you have three cells A1, A2 and A3 formatted as time (hh:mm), and you have :
A1 = 02:30
A2 = 10:15
A3 has the formula: =SUM(A1:A2)
then A3 will have the correct values of 12:45.
However if your total times are greater than 24 hours then things get tricky.
If you have :
A1 = 15:30 and A2 = 10:15 then A3 will have 01:45 instead of 25:45. I.e. any hours beyond 24 hours are dropped.
To retain the hours beyond 24, you have two alternatives:
1. If you want to convert the hours beyond 24 into days then use this cell format : dd:hh:mm. The total in the previous example will be 01:01:45 ( 1day , 1 hour and 45 minutes)
2. If you want to keep the hours beyond 24 as is then use this cell format :[h]:mm. The total in the previous example will be 25:45.
In both cases the formula will remain as it is. Only the cell format differs.
See another example below:
More posts on time:
http://www.exceldigest.com/myblog/2009/02/08/how-to-add-or-sum-times/
http://www.exceldigest.com/myblog/2008/12/04/how-to-create-a-custom-time-format/
Thank you for your response, it was useful.
How do I add the following time:
Start Finished hrs wked
20:00 02:00
Please.
Thanks in advance
for all your help
January 4th, 2010 at 5:10 pmNana
Since your times span into two separate days then you have to include the day in your time.
The value of the cell will be something like this : 1/5/2010 20:29 , and the format like this: m/d/yyyy hh:mm
Then the formula for hrs wked will be : Finished – Start
January 5th, 2010 at 5:36 pm