Converting Time to HR:MIN:SEC

  • Thread starter Thread starter Mel
  • Start date Start date
M

Mel

Here's what I'm trying to do:

I have something timed in minutes and put it into excel
as "2000". I want Excel to change it to "33:20:00". I
tried changing the format and it gives me some weird date.
And I tried the funtion "=TIME(hr, min, sec) which worked
ok until I hit the 24 hr mark and then it started back at
1.

Any suggestions....
 
One way:

A1: 2000
A2: =A1/(60*24) ==> 33:20:00

when formatted with Format/Cells/Number/Custom [h]:mm:ss

or, more efficiently,

A2: =A1/1440

Since XL stores times as fractional days, you just need to divide
hours by 24. To get hours from minutes, obviously, divide minutes by
60.

when formatted with Format/Cells/Number/Custom [h]:mm:ss
 
Thanks,

But now what if I want to break it up. I want 33 to be in
one cell, the 20 in the next, and the 00 in another?

-----Original Message-----
One way:

A1: 2000
A2: =A1/(60*24) ==> 33:20:00

when formatted with Format/Cells/Number/Custom [h]:mm:ss

or, more efficiently,

A2: =A1/1440

Since XL stores times as fractional days, you just need to divide
hours by 24. To get hours from minutes, obviously, divide minutes by
60.

when formatted with Format/Cells/Number/Custom [h]:mm:ss



Mel said:
Here's what I'm trying to do:

I have something timed in minutes and put it into excel
as "2000". I want Excel to change it to "33:20:00". I
tried changing the format and it gives me some weird date.
And I tried the funtion "=TIME(hr, min, sec) which worked
ok until I hit the 24 hr mark and then it started back at
1.

Any suggestions....
Click to expand...
.
Click to expand...
 
To get 33

=INT(A1/60)

to get 20

=MINUTE(A1/1440)



--

Regards,

Peo Sjoblom


mel said:
Thanks,

But now what if I want to break it up. I want 33 to be in
one cell, the 20 in the next, and the 00 in another?

-----Original Message-----
One way:

A1: 2000
A2: =A1/(60*24) ==> 33:20:00

when formatted with Format/Cells/Number/Custom [h]:mm:ss

or, more efficiently,

A2: =A1/1440

Since XL stores times as fractional days, you just need to divide
hours by 24. To get hours from minutes, obviously, divide minutes by
60.

when formatted with Format/Cells/Number/Custom [h]:mm:ss



Mel said:
Here's what I'm trying to do:

I have something timed in minutes and put it into excel
as "2000". I want Excel to change it to "33:20:00". I
tried changing the format and it gives me some weird date.
And I tried the funtion "=TIME(hr, min, sec) which worked
ok until I hit the 24 hr mark and then it started back at
1.

Any suggestions....
Click to expand...
.
Click to expand...
Click to expand...
 
One way:

A1: 2000
B1: =INT(A1/60)
C1: =MOD(INT(A1),60)
D1: =MOD(A1,1)*60

This assumes that any fractional amounts represent fractional
minutes, e.g.:

A1: 2001.75
B1: 33
C1: 21
D1: 45

FWIW - it would be helpful if you stated your actual requirements
first, rather than playing "bring me a rock".
 
Back
Top