Forum Discussion
Solved
Display number of days in Custom Format
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.
Hi v-josephc
you describe a very common issue and unfortunately Excel does still not allow to activate sheet protection while still being able to use the formatted tables functionality (e.g. copy formulas).
The only advise that I have is to add already enough blank rows in your table, so the users would not have to do that by themself. And then activate the sheet protection to lock the formula cells.
It's not ideal and maybe not practical in your situation, but I have no better advise.
1 Reply
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)"
