Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Convert number to days - hours - minutes

1. I agreed to these rules
Join Date
5th November 2004
Location
San Francisco
Posts
15

## Convert number to days - hours - minutes

Hello Excel Users

I would like to convert a number (73.60) to days, hours & minutes to track how much sick or vacation time I have.

Thanks
Robert

Excel Video Tutorials / Excel Dashboards Reports

2. This is fairly easy to do in Excel. Excel stores dates and time as numbers with each integer equal to one day. So 0.5 is 12 hours, for example. 1.5 would equal 36 hours.

What units is your 73.60 in? If it's hours, divide it by 24 to convert it to Excel days and set your cell's format to CUSTOM > dd:hh:mm

If it's in days already, just set the format.

EDIT: Oops, I should have tested first. This format does not work for greater than 24 hours. Some fancier formulas are, I think, needed.

[h]:mm will display hours (beyond 24) and minutes, but this is not what you asked.

3. Adendum. If your 73.60 is in hours in cell A1, then in another cell (I'll use C1), enter:

=INT(A1/24)

to get the number of days.

format another cell to hh:mm and enter:

=(A1-24*D1)/24

This will display the hours and minutes.

Also see Dave's tutorial on working with Dates and Times.

http://www.ozgrid.com/Excel/ExcelDateandTimes.htm

And (I forgot to say) Welcome to OzGrid

6. Cool Tomach but just for clarity, I think you meant:
Originally Posted by thomach
format D1 to hh:mm and in D1 enter:

=(A1-24*C1)/24

7. I agreed to these rules
Join Date
5th November 2004
Location
San Francisco
Posts
15

## Re: Convert number to days - hours - minutes

That was awesome!

Thank you very much thomach & Barbarr.

Excel Video Tutorials / Excel Dashboards Reports

8. I agreed to these rules
Join Date
27th May 2015
Posts
1

## Re: Convert number to days - hours - minutes

May I ask help if the format will be like this 1 d 6 h 15 m . How could I make it appear only in hours and minutes. well it should look like 30h 15 m. Thank you in advance.

Excel Video Tutorials / Excel Dashboards Reports

9. ## Re: Convert number to days - hours - minutes

Hi nicedoll.
welcome to the forum

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