Tell me more ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.
up vote 6 down vote favorite
3

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)

share|improve this question
1  
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
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). – Zman771 Nov 17 '11 at 20:26
does anyone have a solution? – Zman771 Dec 5 '11 at 16:11

2 Answers

up vote 13 down vote accepted

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: enter image description here

share|improve this answer
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? – Zman771 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. – Kaze Oct 26 '11 at 18:24
+1 for the extensive and clear explanation :) – Tiago Cardoso Oct 26 '11 at 19:08
3  
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) – Zman771 Nov 14 '11 at 19:55
1  
@LongTTH Enclose them in <kbd></kbd> tags. :) – Kaze Nov 17 '11 at 15:56
show 2 more comments
up vote 4 down vote

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"
share|improve this answer
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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