Adding up hours in Excel?

[styley]

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?

[MarkRae]

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.

[styley]

Thank you Mark.

[styley]

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.

[HowardKennitby]

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.

[styley]

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.

[MarkRae]

Assuming the daily totals are in A1, A2, A3 etc:

=SUM(A1:A7)*24

[styley]

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.

[MarkRae]

So format the cell which contains the total to display one or more decimals...

[styley]

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.

[ChuckFickens]

Custom format the result cell to [h]:mm otherwise it rolls over at 24hours and starts at 0 again.

[ChuckFickens]

Oppps...

Ignore me, I didn't read the question properly.

[cathfromsaron]

shouldn't you be taking your start time from your finishing time, not adding? and then adding up the hours in the final column?

[cathfromsaron]

ah, just seen your initial advice MR x

[ChuckFickens]

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.

[styley]

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.

[ChuckFickens]

Have a look at this...

http://s000.tinyuploa...356013857362913525845

[MarkRae]

I'm not sure what you're doing:
http://tinypic.com/r/33o02eg/5

[ChuckFickens]

Actually, this works better, the last one fell apart with times over 24hrs when converting to decimal.

http://s000.tinyuploa...018856571303109233216

[styley]

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?

[styley]

I might be getting somewhere now. Thanks for all the help, seriously.