Convert

[Guest]

How do I convert a cell showing 2.97 into 3 hours and 37 minutes and place 3
in one column and 37 in the other column...make sense???

 

[Sandy Mann]

=(INT(H20)+MOD(H20,1)/0.6)/24

Format as time.

Out of curiosity What are you doing?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Guest]

aghhh...I just figured that out, thank you. How do I take the single cell
and transfer it into Hours in one cell and minutes in the other for 2.58??
Again, thank you for your help. We have a time sheet that doesn't export it
into hours and minutes.

 

[Ron Rosenfeld]

How do I convert a cell showing 2.97 into 3 hours and 37 minutes and place 3
in one column and 37 in the other column...make sense???

A1: 2.97

If you have the ATP (analysis tool pak) installed:

=INT(DOLLARDE(A1,60)) --> 3
=MOD(DOLLARDE(A1,60),1)*60 --> 37

If you don't, then:

=INT(INT(A1)+MOD(A1,1)/0.6) --> 3
=MOD(INT(A1)+MOD(A1,1)/0.6,1)*60 --> 37



--ron

 

[Sandy Mann]

Hours: =INT(INT(H20)+MOD(H20,1)/0.6)/24

Minutes: =MOD(MOD(H2:H20,1)/0.6,1)/24

This will return 3:00 and 0:37 respectively

If you want the hours and minutes as dicimal figures then do not divide by
24 and format as General or Number but that may give you problems lated if
you are adding up the minutes.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[David Biddulph]

=INT(A1) for the hours
=MOD(A1,1)*60 for the minutes.

 

[Ron Rosenfeld]

=INT(A1) for the hours
=MOD(A1,1)*60 for the minutes.

That does not give what the OP requested:

OP 2.97--> 3hrs 37 min

DB 2.97--> 2hrs 58.2 min
--ron

 

[David Biddulph]

You snipped from my message the part where the OP realised that his 3:37 was
wrong and that he wanted 2:58.

"> aghhh...I just figured that out, thank you. How do I take the single
cell

 

[Ron Rosenfeld]

You snipped from my message the part where the OP realised that his 3:37 was
wrong and that he wanted 2:58.

Ah Ha!

That message of the OP did not come through on my newsreader until after I had
posted my message back to you.

From his original, I thought the decimal was separating hours and minutes;
rather than hours and fraction of hour.
--ron