Expiry Date IFTHEN?

[BBC123]

Hi!

I am trying to figure out a formula for the following (hopefully I explain it correctly)
I currently have a sheet where the date of training is entered, and the expiry date is then auto-filled based on the first of the next month of the following year (some have 2 year anniversary dates) this is the formula I have : =IF(F13=0,"",DATE(YEAR(F13)+1,MONTH(F13)+1,1))

However, I am looking to see if there is a way to add another statement for this reason: - If the training is done within 90 days of the expiry date, the anniversary date will stay the same but a year would be added on. So, for example; if training was done on March 5, 2020 - the anniversary date would be April 1, 2021. However, if the student completed it in February 2021, the expiry would still be April 1, 2022.

Hopefully there's something out there that can capture this.

Thanks!

 

[etaf]

how does excel know that the course was completed early
F13 is the Date of training
the Cell with the formula in provides an expiry date, 1 year and 1 month after F13 date of training is entered.

lets say the date of training is 20th Jan 2018 - then the expiry would be 1st Feb 2019 - 1 year 1 month
How do we see that the date the course was taken was on 10th Dec 2018 ?

If still entering OVERWRITING in the same cell F!£ , then i think you will need VBA to store the date in cell F13 and compare with the new date overwritten to see if its 90 days before the expiry worked out by the formula and then which will also have to be VBA

 

[BBC123]

how does excel know that the course was completed early
F13 is the Date of training
the Cell with the formula in provides an expiry date, 1 year and 1 month after F13 date of training is entered.

lets say the date of training is 20th Jan 2018 - then the expiry would be 1st Feb 2019 - 1 year 1 month
How do we see that the date the course was taken was on 10th Dec 2018 ?

If still entering OVERWRITING in the same cell F!£ , then i think you will need VBA to store the date in cell F13 and compare with the new date overwritten to see if its 90 days before the expiry worked out by the formula and then which will also have to be VBA

Hi Etaf!
Yes, I would like it to be able to overwrite in F13, I am open to a VBA code if there is one, I’m familiar with macros but have never done a code for this.

do you have any ideas that would Make this work? It’s a spreadsheet for ongoing training over the course of 5ish years.

so, if the F13 gets updated with the new date of training, is it possible for the expiry date cell to either;
1) updated to the 1st of the next month, OR;
2) if within 90 days, keep the original due date, but add a year.

would you know how to code that?

Any help would be appreciated

thanks!

 

[etaf]

i'm afraid i don't know VBA well enough to offer advice