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

  • Thread starter Thread starter Midjack
  • Start date Start date
M

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
 
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 said:
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).
Click to expand...

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 -----
 
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
 
Fred Smith said:
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 said:
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.
Click to expand...
Click to expand...
 
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.

Fred Smith said:
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 said:
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.
Click to expand...
Click to expand...
 
Back
Top