Donate SIGN UP

Adding up hours in Excel?

Avatar Image
styley | 12:27 Wed 07th Dec 2011 | How it Works
25 Answers
I am using Excel 2003. How can I work out the total between two times. For example if I put in 09:00-13:30, what function can I use to work out that the total hours is 4.5, or can this be done?
Gravatar

Answers

1 to 20 of 25rss feed

1 2 Next Last

Best Answer

No best answer has yet been selected by styley. Once a best answer has been selected, it will be shown here.

For more on marking an answer as the "Best Answer", please visit our FAQ.
Do yourself a HUGE favour and place the start time and end time in separate cells. Then it's simply a matter of subtracting one from the other (e.g. =B1-A1) and formatting the cell to display hours and minutes.
Question Author
Thank you Mark.
Question Author
I am now having a problem adding up all of the hours together. For some reason it will not work out 30 minutes as 0.5 of an hour so if I put in the times from say 09:00 to 13:30 it will be 04:30 hours in time but in a number it will be 5 rather than 4.5 hours.
I have just done a trial in XL2003 and use all cells formatted to Custom. In the list of options there is one hh:mm. This does what I think you want.
Question Author
But then how do you add all of the hours worked in a day to give the total hours for week. I can't seem to find a way.
Assuming the daily totals are in A1, A2, A3 etc:

=SUM(A1:A7)*24
Question Author
So can I have the times set as time but then have the total hours as a number based on the times. When I am trying to do this it does not see the 30 minutes as 0.5 so the total hours will add up wrong. For some reason it seems to round the numbers up.
So format the cell which contains the total to display one or more decimals...
Question Author
Don't know what is going wrong Mark. When I have the times 09:00 to 14:30 and work out the hours it comes up as 05:30 which is correct. But when I try to change it to number it comes up as 1.23 where I want it to be 5.5.
Custom format the result cell to [h]:mm otherwise it rolls over at 24hours and starts at 0 again.
Oppps...

Ignore me, I didn't read the question properly.
shouldn't you be taking your start time from your finishing time, not adding? and then adding up the hours in the final column?
ah, just seen your initial advice MR x
As far as I can see you need the result of the totaled time in one cell using the [h]:mm as a custom format (so it works if you go over 24hrs) and then in another cell put (this assumes the result cell is C4)

=(C4-INT(C4))*24

This should then give you a result in decimal.
Question Author
Yes, I noticed that was happening chuck but when I do the thing you said and tried it on two times it didn't work properly. I added 08:00 and 05:30 and got 61:30. The other way it would add okay to 13:30 but as you say, after adding a few more hours it would roll over to 02:00 or something like that. Man this is awkward.
I'm not sure what you're doing:
http://tinypic.com/r/33o02eg/5
Actually, this works better, the last one fell apart with times over 24hrs when converting to decimal.

http://s000.tinyuploa...018856571303109233216
Question Author
Whatever I'm doing I can't seem to get it the same way as you guys, even when I copy what you are doing. Can I just ask what does *24 mean, what does it do to the equation?
Question Author
I might be getting somewhere now. Thanks for all the help, seriously.

1 to 20 of 25rss feed

1 2 Next Last

Do you know the answer?

Adding up hours in Excel?

Answer Question >>

Related Questions