Updating the countdown to renewal dates

In my worksheet in range D5:D10 I have 5 renewal dates. The date of the month is fixed , but the month itself should update with time. So as time goes by , 1st April will change to 1st May , 12th April will change to 12th May and so on.

In G5:G10 I have a countdown which shows how many days remain from the current date until the dates in D are reached.

Using Today() , what should happen is that G will reduce over time to 1 day left , and as the month changes from April to May in D , so G will respond by going back to 30 (31) days remaining.

Correctly implemented , this should run perpetually with the renewal dates updating monthly and the countdowns responding accordingly.

Unfortunately , I can't quite get the formula right in G to make this happen reliably. There is some event code and CF involved , but I'm not sure this impacts the core working of the formulas.

I wonder if someone with fresh eyes could have a look at the formulas and code in the attached to see where I'm going wrong. Sample attached.

Always grateful for any advice.

What's wrong with a) the current formula and b)

=D5-TODAY()

I don't quite follow you.

Also:

=IF(DAY(TODAY())>=1,DATE(YEAR(TODAY()),MONTH(TODAY())+1,1),DATE(YEAR(TODAY()),MONTH(TODAY()),1))

since DAY(TODAY()) will ALWAYS be >=1 - why not just use:

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)
varying the bit in red as previously.

Glenn - thanks for getting back.

Yes , I can see that in the formulas in D that

Please Login or Register  to view this content.

will always be >=1. I'll amend that. Thanks

Beyond this , I've managed to find the issue with the formulas in G and eradicated the error messages.

Grateful for your help.