Annual fee renewal formula

[myoung76]

Hello,

I am looking for help. I keep records of the registration date of a professional body of all staff. These dates range from 2011 to present. There is an annual fee due each year on the original date that registration was taken i.e. registration taken on 30/10/2019 therefore annual fee would be due 30/10/2020.

What would be the best solution to highlight amber for fee due within 12 weeks and red due within 6?

thank you all.

Maggie

[PaulM100]

For amber: =TODAY()-A1<=6*7
For red: =TODAY()-A1<=12*7

[myoung76]

Hello, is A1 for the original registration date? Thank you.

[PaulM100]

Yes, I used A1 as you didn't upload a sample

[Sam Capricci]

I would think that PaulM's formula would actually look at the due date rather than the original registration date.

[myoung76]

Hi Sambo, I need a formula to tell me when the annual fee date annually based on the original date of registration. So for someone who registered on 21/10/2015 I would need a formula to tell it is due 21/10/2019 and for someone who registered 21/05/2019 then for 21/05/2020. Perferably amber for due within 12 weeks and red for due within 6

[PaulM100]

Perferably amber for due within 12 weeks and red for due within 6

Sambo is right. But this part is really confusing

[myoung76]

Apologies. that would be a conditional formatting function

[Sam Capricci]

I assumed (as maybe Paul did too?) that you had the original date in one column and the renewal date in another column. So like 9/15/2018 was the last date they renewed, next renewal would be...
you could use almost any formula to calculate it, for example I used =EDATE(A1,12) and that will return 9/15/2019 as the due date for the next renewal. (of course I use the US formatting with MM/DD/YYYY while you use the European of DD/MM/YYYY but it works either way AND that assumes your last date for their last renewal is in col A and your renewal date might be in col B.)

[myoung76]

Perfect! Thank you

[Sam Capricci]

So, are you saying you only have their original join date and that could be something like 10/30/2015 (30/10/2015 in your formatting) and you want the formula to focus on the original date every year, adding whole years to it and counting down from 12 weeks out, then 6 weeks out then once past it goes blank until 12 weeks before 30/10/2020 then again in 2021?
If so I'm wondering if that can be done with CF without VBA, maybe Paul knows?

EDIT: I was typing my reply when you posted your reply in post #10, are you saying Paul's formula got it for you and the issue is solved? If so don't forget to mark the post as solved and thank him.

[myoung76]

Exactly, very well put...thank you both

[PaulM100]

A bit of a stretch but try this:
For 12weeks:

Please Login or Register  to view this content.

for 6 Weeks:

Please Login or Register  to view this content.