SOLVED

Display number of days in Custom Format

Copper Contributor

I have a calculation of the difference between two dates and I'd like to display the answer as d "days" h "hours".  Excel interprets the d as the day of the month of the number of days since 1900, so 179 days is '27' (27 Jun 1900).

 

There is an [h] format for total number of hours (to stop modulo 24) but I can't find an equivalent for days.  I don't want a text format as I need to be able to perform a further calculation / chart the result.

1 Reply
best response confirmed by ChemEnger (Copper Contributor)
Solution

@ChemEnger 

There is no custom format for cumulative days. I'd use one column for the numeric difference, e.g. 179.5, and another with a formula for the display value. For example with the numbers in D2 and down, enter the following formula in E2 and fill down:

=INT(D2)&" day(s) "&TEXT(D2,"h")&" hour(s)"

1 best response

Accepted Solutions
best response confirmed by ChemEnger (Copper Contributor)
Solution

@ChemEnger 

There is no custom format for cumulative days. I'd use one column for the numeric difference, e.g. 179.5, and another with a formula for the display value. For example with the numbers in D2 and down, enter the following formula in E2 and fill down:

=INT(D2)&" day(s) "&TEXT(D2,"h")&" hour(s)"

View solution in original post