Expiry Date IFTHEN?

BBC123

New Member
Joined
Aug 28, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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
 
Upvote 0
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!
 
Upvote 0
i'm afraid i don't know VBA well enough to offer advice
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top