Formatting minutes and adding them together

[poetdi]

Hello - I have seen a similar question recently but not exactly. I need to enter minutes, have them display that way, and then add them together to find a total number of minutes expressed as hours/minutes.

When I type (using Excel 2003) this, for example:

00:30

the formula bar shows 1/30/1900 12:00:00 AM, even if I format it as mm:ss. Not at all sure why this is! Also, what shows in the worksheet is 00:00. (If I change it to a general number it shows as 30.) That's part one.

Part two is: Once the numbers format and display correctly, I'd like to be able to add a column of these together and have the result formatted as hh:mm - a total number of minutes expressed as hh:mm.

Any suggestions will be appreciated. This is for a client who will not understand code beyond what is available in the format/cells dialog, so the simpler the better.

Thanks much!!!
Diane

 

[barry houdini]

When I type (using Excel 2003) this, for example:

00:30

the formula bar shows 1/30/1900 12:00:00 AM, even if I format it as mm:ss. Not at all sure why this is! Also, what shows in the worksheet is 00:00. (If I change it to a general number it shows as 30.) That's part one.

Are you sure? What you say is displayed is consistent with putting 30 in the cell. If you enter 00:30 excel will normally recognise that automatically as 30 minutes.

If you have minutes in that format you can just sum them with a regular sum formula e.g.

=SUM(A1:A10)

format as [h]:mm

 

[VoG]

You need to go to Format > Cells and set the format as Time.


If your calculations could result in values greater than 24 hours you need to set a custom format [h]:mm since what you are calculating is a duration, not a time.

 

[poetdi]

Okay - I was making it too complicated, trying to make a custom mm:ss format. But it was really weird that I would do that and it displayed like I mentioned above.

I changed the format to the regular Time format and that worked much better. Adding them together does require a different format for the total, though, as you mentioned and I am playing with the custom formats for that.

Thank you very much for your time and help. I appreciate it!!
Diane

 

[Scott Huish]

That's because Excel treats time as time. How you use that time is irrelevant to how Excel treats it. As far as Excel is concerned, 30 minutes is 30 minutes into a day, i.e. 12:30:00 AM which is what shows in the formula bar (the same fractional part of a day). This does not mean you can't use it as 30 minutes and format it how you like.

 

[poetdi]

That's what I get for being so literal. Thanks again. I've got the custom format for the total working too. Appreciate everyone's help!
Diane