Convert Days Hours and Minutes to decimal days

**david1987** · 12-13-2012, 07:54 AM

Hi,

I get output in the following formats

- Minutes
- Hours/Minutes
- Days/Minutes
- Days/Hours
- Days/Hours/Minutes

I want to convert this to a decimal of a 24 hour day, for example, 12 hours = 0.5 days.

The format is in the attached spreasheet.

Any help is greatly appreciated.

Thanks

David

**Jakobshavn** · 12-13-2012, 09:13 AM

Try this small User Defined Function (UDF):

Please Login or Register  to view this content.

User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I

ALT-M opens a fresh module

3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the UDF from Excel:

=DayFrac(A1)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or

http://www.cpearson.com/excel/Writin...ionsInVBA.aspx

for specifics on UDFs

Macros must be enabled for this to work!

**david1987** · 12-13-2012, 11:39 AM

Great thanks!

**Mudhen459** · 05-06-2015, 05:44 PM

Hello,

I too am looking for a calculation to convert a known period of time from a report I receive monthly. I'm my case, it's a report that shows mean time to repair for IT Service Desk tickets. The report provides me the data in days, hours, minutes, and seconds. I created the UDF per your instructions, but I don't believe I'm using the UDF correctly. What is the proper format I need to use for the calculation to work? I attempted 15:8:10:34 in cell A1 to represent 15 days, 8 hours, 10 minutes, and 34 seconds, but the calculation field (I'm putting the formula in cell A2), but the returned value is '0'. Can you please assist with what I'm doing wrong?

Thanks in advance for all your help!

Jon