Forum Discussion
misulesku
Dec 11, 2022Copper Contributor
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 ...
HansVogelaar
Dec 11, 2022MVP
How would you like to display for example December 28, 2022?
misulesku
Dec 11, 2022Copper Contributor
HansVogelaar January 1st, 2023
- HansVogelaarDec 11, 2022MVP
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.
- misuleskuDec 11, 2022Copper ContributorI'm not quite certain I entirely undestand, could you please give me an example of what you mean?
- HansVogelaarDec 12, 2022MVP
See the attached demo workbook