Formatting minutes and adding them together

poetdi

Board Regular
Joined
Nov 28, 2002
Messages
70
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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

Legend
Joined
Jun 19, 2002
Messages
63,650
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

Board Regular
Joined
Nov 28, 2002
Messages
70
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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Board Regular
Joined
Nov 28, 2002
Messages
70
That's what I get for being so literal. :cool: Thanks again. I've got the custom format for the total working too. Appreciate everyone's help!
Diane
 

Forum statistics

Threads
1,137,205
Messages
5,680,184
Members
419,887
Latest member
Vasokir

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top