Respecting monthly anniversaries

[CDandVinyl]

My worksheet needs to update the dates in D5:D10 to the next month once each date is reached. Presently , I'm using Today() to regulate this, but I'm having trouble with the logic of using this to update variant date criteria.

So for cells with renewal dates on the 1st of the month , it should update to the 1st of the next month once reached. For those showing the 7th of the month it would wait until the 7th and update at that point. In other examples , 12th April becomes 12th May , 13th April becomes 13th May and so on.

Cells G5:G10 show a countdown of days until the next renewal date is due.

The exception in my worksheet is in D8 , which has a four-weekly cycle. I think this is working fine so no change needed with D8

I wonder if someone could suggest a way of having each cell update to their monthly anniversaries.

I've attached a sample file.

Grateful for any help.

[JustinOz20]

Hi,

I think you need to use the EDATE formula.

I placed the first date of interest in A1 (eg 2nd January 2023), then in D5 to have the next 2nd of each month returned enter:
=IF(EDATE(A1,MONTH(TODAY())-MONTH(A1))-TODAY()<0,EDATE(A1,MONTH(TODAY())-MONTH(A1)+1),EDATE(A1,MONTH(TODAY())-MONTH(A1)))

The if calculates if the date is already passed, in G5:
=D5-TODAY()
counts down the days.
HTH
Justin

[CDandVinyl]

JustinOz20 - thanks. I tried the formula as suggested , but it's giving #NAME? errors I'm afraid. It may be my version of XL , I can't be sure.

[JustinOz20]

Not sure, but if you have the edate function you should be able to work it out. I did get a name error when I forgot to add the () brackets on the today() function.

[CDandVinyl]

Thanks JustinOz20 - This is a great help.