Dec 11 2022 11:24 AM
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.
Dec 11 2022 01:32 PM
How would you like to display for example December 28, 2022?
Dec 11 2022 02:40 PM
@Hans Vogelaar January 1st, 2023
Dec 11 2022 03:05 PM
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.
Dec 11 2022 03:15 PM
Dec 11 2022 11:18 PM
See the attached demo workbook