Time Calculator

[Me]

I would like to use Excel to sum up the track times for a music CD. How can
I do this?

Thanks,

Marquez

 

[Orlando Magalhães Filho]

Hi,

For example, on A1, A2, A3 cells type the track times in mm:ss. Maybe Excel
automatically formats the cells as mm:ss. if not, do this.
On A4 cell insert this formula: =SUM(A1:A3) and format this cell as [mm]:ss.
To format a cell select it, go Format menu > Cells... command > Number tab >
Custom category and insert the format.

HTH

 

[J.E. McGimpsey]

If your track times are entered as times, e.g., 0:03:35, in A1:A15,
then the total is just =SUM(A1:A15).

 

[Me]

I have tried each of these solutions before. The result was an AM or PM
listing in the cells.

 

[J.E. McGimpsey]

I have tried each of these solutions before. The result was an AM or PM
listing in the cells.

XL stores both time of day and elapsed time in the same way - as
fractional days. 3:00:00 of elapsed time is stored the same as
3:00:00 AM = 0.125. The only difference is in the displayed format.

Simply change the format of your cells:

Format/Cells/Number/Custom [mm]:ss

to keep time in minutes rather than rolling over into hours.

[hh]:mm

to keep time in hours rather than rolling over into days.

 

[Me]

Hi J.E.,

I created a new XL file. I formatted Column B as follows:
Custom, mm:ss.

I then entered 5:00 in both Rows 2 & 3. I summed them up in Row 5. The
following is the output that I received in column B:

Row 2 - 00:00
Row 3 - 00:00
Row 5 - 00:00

What am I doing wrong?

G.Y. Marquez

I have tried each of these solutions before. The result was an AM or PM
listing in the cells.

XL stores both time of day and elapsed time in the same way - as
fractional days. 3:00:00 of elapsed time is stored the same as
3:00:00 AM = 0.125. The only difference is in the displayed format.

Simply change the format of your cells:

Format/Cells/Number/Custom [mm]:ss

to keep time in minutes rather than rolling over into hours.

[hh]:mm

to keep time in hours rather than rolling over into days.

 

[Orlando Magalhães Filho]

Hi,

If you enter only 5:00 Excel understand 05:00:00 (05 hours, 00 minutes and
00 seconds). Try to enter 00:05:00 (0 hours, 05 minutes and 00 seconds). And
in the row 5 format as [mm]:ss.

Regards,

Orlando

Hi J.E.,

I created a new XL file. I formatted Column B as follows:
Custom, mm:ss.

I then entered 5:00 in both Rows 2 & 3. I summed them up in Row 5. The
following is the output that I received in column B:

Row 2 - 00:00
Row 3 - 00:00
Row 5 - 00:00

What am I doing wrong?

G.Y. Marquez

I have tried each of these solutions before. The result was an AM or PM
listing in the cells.

XL stores both time of day and elapsed time in the same way - as
fractional days. 3:00:00 of elapsed time is stored the same as
3:00:00 AM = 0.125. The only difference is in the displayed format.

Simply change the format of your cells:

Format/Cells/Number/Custom [mm]:ss

to keep time in minutes rather than rolling over into hours.

[hh]:mm

to keep time in hours rather than rolling over into days.

 

[Me]

Orlando,

Thanks a million!!!! That worked nicely.

Last question.
Does anyone have a macro that can automatically add "00:" to the time string
and strip the trailing ":00" from the cell contents. In this way, I can
copy track times into the cells and then simply add them up.

Thanks,

G.Y.

Hi,

If you enter only 5:00 Excel understand 05:00:00 (05 hours, 00 minutes and
00 seconds). Try to enter 00:05:00 (0 hours, 05 minutes and 00 seconds). And
in the row 5 format as [mm]:ss.

Regards,

Orlando

Hi J.E.,

I created a new XL file. I formatted Column B as follows:
Custom, mm:ss.

I then entered 5:00 in both Rows 2 & 3. I summed them up in Row 5. The
following is the output that I received in column B:

Row 2 - 00:00
Row 3 - 00:00
Row 5 - 00:00

What am I doing wrong?

G.Y. Marquez

or

PM

listing in the cells.

XL stores both time of day and elapsed time in the same way - as
fractional days. 3:00:00 of elapsed time is stored the same as
3:00:00 AM = 0.125. The only difference is in the displayed format.

Simply change the format of your cells:

Format/Cells/Number/Custom [mm]:ss

to keep time in minutes rather than rolling over into hours.

[hh]:mm

to keep time in hours rather than rolling over into days.

 

[Dave Peterson]

If you have a bunch of existing cells, you can convert them like this:

put 60 in an empty cell.
copy it to the clipboard (edit|copy)
select your range to convert
edit|paste special|check divide in the Operation section

Or you could try this macro (select your range to convert first):

Option Explicit
Sub testme()

Dim myCell As Range
Dim myRng As Range

Set myRng = Selection
For Each myCell In myRng.Cells
With myCell
If IsEmpty(.Value) = False Then
If IsNumeric(.Value) Then
If .Value > 1 / 24 Then '1 hour
.Value = .Value / 60 'convert from hours to minutes
.NumberFormat = "[mm]:ss"
End If
End If
End If
End With
Next myCell

End Sub

About the only check it does is to see if the value is greater than an hour
(1/24=.0416...) You can see that if you format a cell with time in it as
general.

Orlando,

Thanks a million!!!! That worked nicely.

Last question.
Does anyone have a macro that can automatically add "00:" to the time string
and strip the trailing ":00" from the cell contents. In this way, I can
copy track times into the cells and then simply add them up.

Thanks,

G.Y.

Hi,

If you enter only 5:00 Excel understand 05:00:00 (05 hours, 00 minutes and
00 seconds). Try to enter 00:05:00 (0 hours, 05 minutes and 00 seconds). And
in the row 5 format as [mm]:ss.

Regards,

Orlando

Hi J.E.,

I created a new XL file. I formatted Column B as follows:
Custom, mm:ss.

I then entered 5:00 in both Rows 2 & 3. I summed them up in Row 5. The
following is the output that I received in column B:

Row 2 - 00:00
Row 3 - 00:00
Row 5 - 00:00

What am I doing wrong?

G.Y. Marquez


I have tried each of these solutions before. The result was an AM

or

PM
listing in the cells.

XL stores both time of day and elapsed time in the same way - as
fractional days. 3:00:00 of elapsed time is stored the same as
3:00:00 AM = 0.125. The only difference is in the displayed format.

Simply change the format of your cells:

Format/Cells/Number/Custom [mm]:ss

to keep time in minutes rather than rolling over into hours.

[hh]:mm

to keep time in hours rather than rolling over into days.