Forum Discussion
How to format a number in excel to show more than 31 days?
Dear all, great day!
Please note that i'm trying to format my data number to show more than 31 days, but excel can't accept this
The number is format to d:h:min (days, hours and minutes) i would like to format as m:d:h:min (month, days, hour, min) is that possible?
Bellow show what happens when i add a day in a sequence wich is already with 31 days.
And below show how i would like to be:
Appreciate your reply.
2 Replies
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.
Months and days are always calendar-based in Excel's number formats.
Perhaps there is an alternative, but it'd be handy to see a sample workbook. Could you attach one to a reply, or if that is not possible, make it available through OneDrive/Google Drive/Dropbox?
