Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Convert Decimal Times to Hours & Minutes

1. Member
Join Date
28th October 2005
Posts
58

## Convert Decimal Times to Hours & Minutes

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

Excel Video Tutorials / Excel Dashboards Reports

2. ## Re: Separating values into different columns

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

3. Super Moderator
Join Date
26th January 2003
Location
Derbyshire,UK
Posts
19,470

## Re: Separating values into different columns

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"
Last edited by royUK; October 28th, 2005 at 18:02.

4. Established Member
Join Date
14th September 2004
Location
Surrey, England
Posts
802

## Re: Separating values into different columns

If the hours are stored as decimal in A1

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

Alan.

Excel Video Tutorials / Excel Dashboards Reports

5. ## Re: Separating values into different columns

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
Last edited by Dave Hawley; October 28th, 2005 at 18:04.

6. Member
Join Date
28th October 2005
Posts
58

## Re: Separating values into different columns

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

Cheers
salP

Excel Video Tutorials / Excel Dashboards Reports

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno