Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Convert Number To Hours & Minutes

1. ## Convert Number To Hours & Minutes

How can I convert an ordinary number i.e. 3.45 to Hours & Minutes?

A colleague has columns of data that when summed are all wrong i.e. 8.65 instead of 9 Hours 5 minutes.

Excel Video Tutorials / Excel Dashboards Reports

2. Super Moderator
Join Date
7th December 2005
Location
Hampshire, England
Posts
4,898

## Re: Convert Number To Hours & Minutes

Not much to go on, Timbo. Are we talking 3.45 decimal, 3.45 hours, or 3.45 days? See Excel Dates & Times.

Excel Video Tutorials / Excel Dashboards Reports

3. ## Re: Convert Number To Hours & Minutes

Formatted as a number to two decimal places.

Excel Video Tutorials / Excel Dashboards Reports

4. Super Moderator
Join Date
7th December 2005
Location
Hampshire, England
Posts
4,898

## Re: Convert Number To Hours & Minutes

Excel Video Tutorials / Excel Dashboards Reports

5. ## Re: Convert Number To Hours & Minutes

See attached.

Excel Video Tutorials / Excel Dashboards Reports

6. Super Moderator
Join Date
7th December 2005
Location
Hampshire, England
Posts
4,898

## Re: Convert Number To Hours & Minutes

OK, Assuming these are hours, so 3.45 needs to show as 3:27, you need to divide by 24, and format as Custom [h]:mm

Excel Video Tutorials / Excel Dashboards Reports

7. ## Re: Convert Number To Hours & Minutes

Why 3.27? 3.45 represents 3 hours 45 minutes.

In the sample workbook that I attached and said earlier 8.65 should equal 9 hours 5 minutes.

Excel Video Tutorials / Excel Dashboards Reports

8. ## Re: Convert Number To Hours & Minutes

why not enter the times in the right format to begin with and write 3:45? this way xl will automatically assume it's a time. 8:65 however will not be recognized. That really does not make sense.

9. ## Re: Convert Number To Hours & Minutes

Originally Posted by Timbo
A colleague has columns of data that when summed are all wrong i.e. 8.65 instead of 9 Hours 5 minutes.
hi Timbo,

what you should do is correct the mistake yr colleague did before summing them up

try this formula to convert them to time
then you should be able to get the correct sum:

=SUBSTITUTE(A1,".",":")

where A1 is the wrong entry.

hth

10. Super Moderator
Join Date
7th December 2005
Location
Hampshire, England
Posts
4,898

## Re: Convert Number To Hours & Minutes

Try this, applied to A12:

=(INT(A12)*60+MOD(A12,1)*100)/60/24

and format as Custom [h]:mm

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