In Excel, given a number representing some amount of time (e.g. 25.5 hours), is there a way to format it so that it shows as 1d 1h 30m?
Bounty question :
Is there a way to add months to this format? e.g. 1M 1d 1h 1m. The problem with the current format is it doesn't handle anything over 32 days (ie 32.5 is formatted as 1d 8h 0m)
You're going to have to convert those hours into something Excel can understand.
Assuming your hours are in decimal form (e.g. 25.5 hours, 37.25 hours), here's how you can do it:
Step1:
Divide the amount by 24.
This gets the hour-day ratio, or converts the amount to a fraction of a day (e.g. 25 hours converts to 1 1/24 or 1.04).
Step2:
Change the number format of the cell that contains the formula or converted value to Custom
(press Ctrl + 1 > Custom).
Use this format string:
[<=0.0416551] [m]"m";[<1]h"h" m"m";d"d" h"h" m"m"
0.0416551 = 0:59:59 in decimal
or
[<1]h"h" m"m"; d"d" h"h" m"m"
or
d"d" h"h" m"m"
Example:
Ctrl + 1 into post like you? Many thanks!
If the time is in hours, then this is one way:
=TRUNC(A1/24) & "d " & TRUNC(A1-TRUNC(A1/24)*24) & "h " &
60*((A1-TRUNC(A1/24)*24)-TRUNC((A1-TRUNC(A1/24)*24))) & "m"
=TRUNC(A1/24) & "d " & TRUNC(MOD(A1,24)) & "h " & ROUND((A1-TRUNC(A1))*60,0) &"m".
Oct 26, 2011 at 18:20
(cannot add as comment as I don't have 50 reputation)
To get months to display, you can use the following format (note, this doesn't directly address your exact needs, but you may be able to modify this as appropriate). I've also added some colors to my strings to clarify time ranges: In the top example Red for greater than one day, black for within a day. The second example has red for year+, blue-green for between a quarter and a year, and blue otherwise. You can of course use different formatting specifications as you desire.
[Red][>1]yy\y mm\m dd\d hh:mm:ss;[Black][<=1]hh:mm:ss;[Red]-0.000000;[White]@
or
[Red][>=364.5]yy\y mm\m dd\d;[Color50][<=122]mm\m dd\d;[Color5]mm\m dd\d;[Black]@
To format "32.5" as 32d 12:00:00 you could use [d]\d hh:mm:ss, note the brackets, which indicate that you wish to include values greater than 31 for the number of days.
The \ notation instructs excel to specifically include the letter following the \ symbol, rather than interpreting it as metadata for the command (e.g. d, without a slash, would just immediately output the days again). This is equivalent to the " " notation that you see in other examples.
A few limitations and/or bugs:
1) you cannot specify more than 4 formatting types, the last of which needs to be for text-only. This means that only 3 are usable for formatting dates, the 3rd of which cannot contain any greater than/less than comparisons.
2) the mm as months is actually high by 1 when doing a date difference (as of Excel 2010), as Excel actually stores date differences as full DateTimes and there's no 0th month, but rather January is the first month. Days don't seem to have this issue, as "1.1" displays as 1 day and 0.9 will display as 0 days (both with 1 month:P). I don't know a way to do math within the cell style dialogue (e.g. (mm-1)\m), so if you actually want these numbers to be accurate (subtract 1 from month) you'll have to write a vB script to convert it and display the final text string.
3) Excel doesn't handle negative time differences appropriately
Alternatively, you could do the formatting in the cell itself with a formula like
=IF((B2-A2)>1,DATEDIF(A2,B2,"y")&"y "&TEXT(DATEDIF(A2,B2,"ym"),"00")&"m "&TEXT(DATEDIF(A2,B2,"md"),"00")&"d",TEXT(A2-B2,"hh:mm:ss"))
This will display the information as hh:mm:ss if less than a day and y MM dd if greater than a day. This will not be subject to the off-by-one-month error as above. However, this cell is actually text, and is no longer a numeric value. So you can't (directly) do any further math with it.
