Forum Discussion
Would like to change maximum number of days in month
Hello everyone,
I'm trying to make a custom format of date which will automatically switch to the next month after reaching the number 27. Would it be possible to even do this in Excel?
Thanks for useful answers in advence.
How would you like to display for example December 28, 2022?
HansVogelaar January 1st, 2023
You cannot do this with a custom format.
You can use a formula to return the modified date in another cell.
For example, if you have dates in D2 and down, enter the following formula in another cell in row 2, for example E2:
=IF(D2="","",IF(DAY(D2)>27,EOMONTH(D2,0)+1,D2))
Format the cell with the formula as a date, then fill down.
P.S. Depending on what exactly you want, you may have to use >=27 instead of >27.
