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

Question

Level 1

24 points

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

Hi, supposing I create an invoice with a date "03-04-25" (A4) and, in another cell I would like to add the due date as the 20th of the next month, is there a way to do this as a single string so that the result is 'Due 20-05-25"

My attempt ends up like this:

"Due 20-"&SUM(MONTH(A4)+1)&"-"&YEAR(A4)

and the result is "Due 20-5-2025"

I'd ideally like the result to be "Due 20-05-25" so I must have to apply some formatting somewhere?

I'm probably doing this the hard way, but this can't be an uncommonly used idea, so I'm hoping there's a solution out there!

Many thanks.

Posted on May 12, 2025 11:50 PM

Reply

Answer 1

Top-ranking reply

Camelot

Level 9

58,269 points

May 13, 2025 11:36 AM in response to actionman99

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.

Reply

Answer 2

SGIII

Level 8

37,532 points

May 13, 2025 1:26 AM in response to actionman99

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

Reply

Answer 3

SGIII

Level 8

37,532 points

May 13, 2025 7:47 AM in response to actionman99

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

Reply

Answer 4

Camelot

Level 9

58,269 points

May 14, 2025 10:09 AM in response to actionman99

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.

Reply