Text AND Date formula for the 20th of the following month

I took a different (simpler?) approach and eschewed all the text parsing.

Instead, for a given date in Cell B2, I set C2 to

=EDATE(B2,1)

This simply adds one month to the date in cell B2.

Then I applied a custom format to that cell where the format adds the text "Due: 20-" followed by the month and year:

Done.

The thing I like about using custom format for the 'Due:' part of the field is that, as far as Numbers is concerned, it's still a date field. Therefore any functions that rely on a date will continue to work.

For example, if you want to add conditional highlighting for cells that are due soon, or overdue, you can do this. Likewise sorting or filtering the field is easy with inherent date cells.

Try this:

="Due 20-" & RIGHT("0"&MONTH(EDATE(A4,1)),2)&"-"&RIGHT(YEAR(EDATE(A4,1)),2)

The EDATE increments by one month and works properly for dates near year end, pushing them into January of the next year.

The RIGHT(leading-zeros&value,1) and RIGHT(leading-zeros&value,1) pad with zeros and then add the last 1 or two digits.

EDATE - Apple Support

RIGHT - Apple Support

SG

You can also use the new LET function to improve readability (a little):

=LET(d,EDATE(A4,1),"Due 20-"&RIGHT("0"&MONTH(d),2)&"-"&RIGHT(YEAR(d),2))

If you use LET to define d as the date one month from the current date in A4, then you can refer to d instead of repeating EDATE.

LET - Apple Support

SG