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)

  • 2
    If a month is 30 days, then you can just adapt the formula of @Lance Roberts. If not, then one must also know the starting date. – harrymc Nov 16 '11 at 19:56
  • 1
    You need to give more details on the format you start with for months, since by your first sentence 32.5 should be in hours. How is it shown differently for days? – Lance Roberts Nov 16 '11 at 20:17
  • 1
    my original question stated hours, but I believe the solution assumes the value is in days: [<0.04] [m]"m";[<1]h"h" m"m";d"d" h"h" m"m". I'm ok with assuming a month is a fixed number of days (e.g. 30 days per month). – RussellZ Nov 17 '11 at 20:26
  • does anyone have a solution? – RussellZ Dec 5 '11 at 16:11

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:

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).

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


[<1]h"h" m"m"; d"d" h"h" m"m"


d"d" h"h" m"m"

Example: enter image description here

| improve this answer | |
  • 1
    absolutely awesome. Thanks! I see that it doesn't show days if it's less than 24 hours. any way to not show hours if it's less than 1 hour? – RussellZ Oct 26 '11 at 18:13
  • I'm trying to figure that out too (my OCD is acting up!). For some reason, when I use "m" by itself in a number format, Excel interprets it as MONTH, not MINUTE, so it keeps displaying 1. – Ellesa Oct 26 '11 at 18:24
  • 4
    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) – RussellZ Nov 14 '11 at 19:55
  • I know this not concern to the question but Would you please tell me how to add Ctrl + 1 into post like you? Many thanks! – Luke Nov 17 '11 at 14:28
  • 1
    @LongTTH Enclose them in <kbd></kbd> tags. :) – Ellesa Nov 17 '11 at 15:56

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"
| improve this answer | |
  • 1
    Bested on that one :). For data that is in a decimal hours count form, creating a new column with the re-formatting formula is indeed the only way (hide the original values if you can). The formula can be simplified a bit: =TRUNC(A1/24) & "d " & TRUNC(MOD(A1,24)) & "h " & ROUND((A1-TRUNC(A1))*60,0) &"m". – kopischke Oct 26 '11 at 18:20
  • Thanks, I knew it could be made simpler, but couldn't figure it out. I'd probably do the formatting also. – Lance Roberts Oct 26 '11 at 18:22

(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.

| improve this answer | |
  • It would have been helpful if you had explained what the backslashes are for. And what's the deal with all the colors? – G-Man Says 'Reinstate Monica' Oct 28 '14 at 22:01
  • I made some edits above. To address your Qs though, the backslashes (on one side) are equivalent to using quotes to set off literal text. The colors are just something that I found useful:P – mpag Nov 7 '14 at 20:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.