Adding time in Excell

[CuriousGeorge2]

I have this little problem. I am adding up hours on a spreadsheet.
The format for the hours is hh:mm_)
I would be adding numbers like:
05:44 (5 hours and 44 min)
08:16 (8 hours 16 min)
07:01 (7 hours 1 min)
06:41 (6 hours 41 min)

when I Total the column with the time the answer it gives me is 3:42 when the answer is really 27 hours and 42 min.
I would like the cell to represent the correct total time. I'm sure it's probobly something silly and easy, that I'm just missing.
Any help would be greatly appreciated.

Thank you,
Rebecca

 

[Dreamboat]

"it gives me is 3:42 when the answer is really 27 hours and 42 min"

Just means you have the cell formatted wrongly.
Format-Cell, choose time and choose the one where the h looks like [h]




Anne Troy

http://www.TheOfficeExperts.com

Word and Excel Macros
Coming soon: wX

 

[CuriousGeorge2]

Thank you! Thank you! Thank you!
Thank you! Thank you! Thank you!


Thank you very much!

 

[Dreamboat]

You're welcome! You're welcome! You're welcome!
You're welcome! You're welcome! You're welcome!

You're welcome very much!

LOL
Always happy to help.

Anne Troy

http://www.TheOfficeExperts.com

Word and Excel Macros
Coming soon: wX

 

[ranebow]

HELP! I've tried this and can't get it to work. My total comes up to 25 hours. When I do an autosum on the column I get 1.0 for the total....which is really 1 day and 1 hour. I don't have an [h] under time....I have to go to custom. When I change the format of this field to that format I come up with 577. What am I doing wrong??????

 

[plantj]

Try
[h]:mm

 

[ranebow]

Tried that and I get 577.

 

[GlennUK]

Are you sure that you're adding times? 577 sounds like the time equivalent of an integer sum total.

What number do you get if you format the cell as 0.00

Glenn.

 

[ranebow]

24.03

 

[plantj]

Type 27:42 into a cell. Then format the cell as [h]:mm.

Does that work at least

 

[GlennUK]

Hi ranebow,

you say that the integer equivalent of your total is 24.03!!!

Well, that is more than 24 days, which is why your time format was saying 577 hours.

Are you sure you are adding times? You said you were expecting a result of around 25 hours, but your current answer could be the result of adding integer hours together instead.

Glenn.

 

[ranebow]

If I type 27:42 in a cell it automatically converts it to 27:42:00. Then I go to formating and select custom [H]:mm:ss and it stays the same.

 

[ranebow]

When I look at the formating of the cells I'm adding, they are formated as TIME 13:30.

 

[Dreamboat]

ranebow: What are you expecting it to say?

Using the brackets around the H make it the number of hours that have "accrued" and NOT the time of day.

In other words, if you add 15:00 and 15:00, that would be 3 pm + 15 hours when the two values are formatted as h:mm, or 6 am.

When the two values are formatted as [h]:mm, and the cell that you add them in is [h]:mm, it will show 30:00 for 30 hours.

Without the bracket, you get a TIME OF DAY. With the bracket you get a NUMBER OF HOURS.

When you use numeric formatting, you get number of days. So, 30 hours formatted as numeric would show 1.25. If you want it to show 30 hours, your formula should including multiplying by 24 so that it shows number of hours and not days.

Anne Troy
Way cool stuff:

http://www.cafeshops.com/OfficeExperts

 

[Dreamboat]

Practical reading:

http://www.cpearson.com/excel/datetime.htm

Anne Troy
Way cool stuff:

http://www.cafeshops.com/OfficeExperts