Forum Discussion
How to format a number in excel to show more than 31 days?
arturp97 wrote: ``i would like to format as m:d:h:min (month, days, hour, min) is that possible?``
No, not with formatting alone. You must use a formula that creates a text result -- which cannot be referenced directly in numeric expressions, if that is your intent.
"d" seems to work for zero to 31 days only by coincidence. "d" formats the day of the month, not number of days.
Moreover, "m" (not after "h" and not in the form "[m]") formats the month of the year, not number of months.
Both assume that the integer part of the cell value is a date "serial number" -- the number of days since 12/31/1899 (for the default date system on Windows).
For a result that looks like days:hrs:mins, you would need an expression of the form:
INT(A1) & ":" & TEXT(A1, "hh:mm")
Appending a number of months is debatable, because the number of days per month varies; namely, 28, 29, 30 and 31. One method:
INT(A1/30.4375) & ":" & INT(A1) - INT(A1/30.4375) & ":" & TEXT(A1, "hh:mm")
where 30.4375 is the average number of days per month in any 4-year period, including a leap year; that is, (3*365 + 366)/48.