Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I'm looking to format a datetime in Excel 2010 to display as something like this:

1.234 ==> 1 day, 5 hour, 36 minutes

I know how to do something similar for simpler scenarios

For just days, I can use a conditional format, like so:

[=1] d" day";d" days"

But this does not work with a timestamp.

For the whole set, I can do:

d" days, "h" hours, "m" minutes"

but this looks funny when one of those values (often days) is 1.

I'm not sure how to fuse these two concepts. I would be extra nice to have

1 day, 5 minutes

if the hours value is 0, but

1 day, 0 hours, 5 minutes

works just as well.

I can whip up a solution with VBA/UDF, but there's no other code on the workbook at this point, so I'd like to avoid that route if possible. I also know I can parse the value out in another cell like this:

=DAY(A1) & IF(DAY(A1)=1," day"," days") & ", " & HOUR(A1) & IF(HOUR(A1)=1," hour"," hours") & ", " & etc...

But this is rather clunky and requires running calculations in a new set of cells, rather than just plugging in the format on the current data column and being done.

Is this actually possible with a conditional number format, or am I stuck with a more long, drawn-out method?

share|improve this question
    
I don't think it is possible to apply a formula to a custom format. What about days over 31? If you tried 32.234 you will get the same result using your whole set. –  wbeard52 Jul 27 '13 at 3:30

2 Answers 2

You can't do this with a custom number format, it is too complicated for simple conditionals like [=1] and you cannot use any functions in the Type box.

You need a formula in a separate cell. (You could, I suppose, hide the original column.)

share|improve this answer
up vote 0 down vote accepted

Based on the feedback here, it looks like this is too much for a simple format. Instead, I went ahead and produced a few formulas to create this.

To display all values, including all 0 values:

=DAY(A1) & IF(DAY(A1)=1," day"," days") & ", " & HOUR(A1) & IF(HOUR(A1)=1," hour"," hours") & ", " & MINUTE(A1) & IF(MINUTE(A1)=1," minute"," minutes") & ", " & SECOND(A1) & IF(SECOND(A1)=1," second"," seconds")

1.25001 : 1 day, 6 hours, 0 minutes, 1 second
2.25001 : 2 days, 6 hours, 0 minutes, 1 second
1.04167 : 1 day, 1 hour, 0 minutes, 0 seconds

To remove trailing 0 values only:

=DAY(A1) & IF(DAY(A1)=1," day"," days") & IF(HOUR(A1) + MINUTE(A1) + SECOND(A1)>0, ", " & HOUR(A1) & IF(HOUR(A1)=1," hour"," hours") & IF( MINUTE(A1) + SECOND(A1)>0, ", " & MINUTE(A1) & IF(MINUTE(A1)=1," minute"," minutes") & IF( SECOND(A1)>0, ", " & SECOND(A1) & IF(SECOND(A1)=1," second"," seconds"), ""), ""), "")

1.25001 : 1 day, 6 hours, 0 minutes, 1 second
2.25001 : 2 days, 6 hours, 0 minutes, 1 second
1.04167 : 1 day, 1 hour

If all 0 values should be omitted, this will work:

=DAY(A1) & IF(DAY(A1)=1," day"," days") & IF(HOUR(A1) + MINUTE(A1) + SECOND(A1)>0, ", ", "") & IF(HOUR(A1)>0,HOUR(A1) & IF(HOUR(A1)=1," hour"," hours") & IF( MINUTE(A1) + SECOND(A1)>0, ", ", ""),"") & IF(MINUTE(A1)>0, MINUTE(A1) & IF(MINUTE(A1)=1," minute"," minutes") & IF( SECOND(A1)>0, ", ", ""),"") & IF(SECOND(A1)>0,SECOND(A1) & IF(SECOND(A1)=1," second"," seconds"),"")

1.25001 : 1 day, 6 hours, 1 second
2.25001 : 2 days, 6 hours, 1 second
1.04167 : 1 day, 1 hour
share|improve this answer

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.