Convert Decimal Times to Hours & Minutes

[salparadise]

Hi,

I need to take a range of times in decimal format (i.e seven and a half hours is 7.5) and separate the hours and the minutes into 2 columns and have them display properly so 7.5 hours would show as 7 hours in one column and 30 minutes in another. I cannot for the life of me find any explanation of how to do this either in the forum or the internet in general.

Many thanks

[richywilson]

Yo Salparadise

Welcome to Ozgrid. Great place to be.

Have you tried Data/Text To Columns.

Select the column or cells (no more than one column wide) and select text to columns under the Data menu.
Use Delimited option and select Other and put . in the box. This will split the dat in to two columns.

Laters

[royUK]

This wil creturn the part hour as minutes- the time (7.5) is in A3
=MOD(A3*60,60) & " minutes"
This will return whole hours
=INT(A3)&" hours"

[A9192Shark]

SalParadise

If the hours are stored as decimal in A1

in B1 type =Trunc(A1)
in C1 type =60*(A1-B1)

Alan.

[Dave Hawley]

Hi salparadise

Welcome to ozgrid

Lets say these decimal times are in A1 onwards, in B1 Enter:
=INT(A1)/24
and format the cell as hh:mm

Now, in C1 Enter:
=MOD(A1,1)/24
and custom format the cell as [m]

Now copy down. You can convert these to values only via Copy Edit>Paste Special - Values

[salparadise]

I appreciate the welcomes and quick responses!
I think i've got it working how I want now thanks to you lot.

Cheers
salP