M
Me
I would like to use Excel to sum up the track times for a music CD. How can
I do this?
Thanks,
Marquez
I do this?
Thanks,
Marquez
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
O
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
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
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).
then the total is just =SUM(A1:A15).
M
Me
I have tried each of these solutions before. The result was an AM or PM
listing in the cells.
listing in the cells.
J
J.E. McGimpsey
Me said: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.
M
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 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
J.E. McGimpsey said:Me said: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.
O
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
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
Me said: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
J.E. McGimpsey said:Me said: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.
M
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.
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.
Orlando Magalhães Filho said: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
orMe said: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. MarquezPMlisting 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.
D
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.
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.
Orlando Magalhães Filho said: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
orMe said: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 AMPM
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.
M
Me
Thanks Dave!
I'll try it and let you know how it works out.
G.Y.
I'll try it and let you know how it works out.
G.Y.
Dave Peterson said: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.
seconds).Orlando Magalhães Filho said: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
AndAMin the row 5 format as [mm]:ss.
Regards,
Orlando
"Me" <[email protected]> escreveu na mensagem
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
orPM
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.