Excel custom format: Date/Time written out?

Question

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?

Answer 1

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

Answer 2

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