Jun 09 2022 06:49 AM
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.
Jun 09 2022 07:16 AM
SolutionThere 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)"
Jun 09 2022 07:16 AM
SolutionThere 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)"