convert minutes and seconds (in a date format) to numbers

[Midjack]

I have a series of times (minutes, seconds) that I would like to convert to
equivalent numbers so they can be graphed. For example 3:11, 3 minutes, 11
seconds (stored as 3:11 am) to seconds (3 x 60 + 11 or 191) or 27:09:00, 27
minutes, 9 seconds (stored as 1/1/1900 3:09:00 AM) or 27 X 60 + 9 = 1629.
Can this be done...easily?

Thanks

 

[Fred Smith]

To convert a time to seconds, multiply by 24*60*60.

Regards,
Fred

 

[Midjack]

Thanks Fred, but this won't work because the data is coded in date format
even though it says minutes and seconds. So by multiplying 3:11 (formatted as
3:11 am) by 24 x 60 x 60, I came up with 9550 --way too much. The answer
should be 191 (3*60+11). I suppose I first have to get the apparent date
format to actual minutes and seconds.

 

[JoeU2004]

Thanks Fred, but this won't work because the data is coded in date format
even though it says minutes and seconds. So by multiplying 3:11 (formatted
as
3:11 am) by 24 x 60 x 60, I came up with 9550 --way too much. The answer
should be 191 (3*60+11).

Well, what Fred had in mind is __a__ date format, too. What you mean is:
you entered minutes and seconds in the form hours:minutes. So multiply by
24*60, and format as General or some other appropriate numeric format.


----- original message -----

 

[Fred Smith]

When you enter n:nn into Excel, it assumes hours and minutes, not minutes
and seconds. As you seem happy with this, just multiply by 24*60 to get
"seconds". Remember, under this method, to display more than 24 "minutes"
you will need to use the format [h]:mm

Regards,
Fred

 

[Midjack]

When you enter n:nn into Excel, it assumes hours and minutes, not minutes
and seconds. As you seem happy with this, just multiply by 24*60 to get
"seconds". Remember, under this method, to display more than 24 "minutes"
you will need to use the format [h]:mm

Regards,
Fred

Thanks Fred, but this won't work because the data is coded in date format
even though it says minutes and seconds. So by multiplying 3:11 (formatted
as
3:11 am) by 24 x 60 x 60, I came up with 9550 --way too much. The answer
should be 191 (3*60+11). I suppose I first have to get the apparent date
format to actual minutes and seconds.

 

[Midjack]

The system apparently went down as I sent my last reply, and when I resent it
there was no reply. So I'll try again.

What I said was a big thanks to you both.

The data I was using came from an outside Internet source labled "time." It
was time duration rather than time itself. When I pasted it into Excel some
of it transposed in time format and some in date format.

The 3:11 example (3 minutes, 11 seconds) was formatted in time as "3:11:00
AM" and had a numerical value of 0.132638889 which when multiplied by 24*60
gave the correct value of 191.

The 27:09 (27 minutes, 11 seconds) example was formatted as the date
"1/1/1900 3:09:00 AM" and had a numerical value of 1.13125 which also gave
the correct value of 1629 when multiplied by 24*60.

So thanks again to you both, and lets hope this reply won't get zapped.

When you enter n:nn into Excel, it assumes hours and minutes, not minutes
and seconds. As you seem happy with this, just multiply by 24*60 to get
"seconds". Remember, under this method, to display more than 24 "minutes"
you will need to use the format [h]:mm

Regards,
Fred

Thanks Fred, but this won't work because the data is coded in date format
even though it says minutes and seconds. So by multiplying 3:11 (formatted
as
3:11 am) by 24 x 60 x 60, I came up with 9550 --way too much. The answer
should be 191 (3*60+11). I suppose I first have to get the apparent date
format to actual minutes and seconds.