Variable Dates

[Talbot123]

Here is the brief set up.

I have one worksheet that is a digital representation of training files. I want to enter exact dates of completed training on this sheet.

The second worksheet is a "training due" worksheet that generates a expiry date based on dates entered in the first sheet. It works pretty well as most training is valid until the 1st day of the 13th month following the training (ie 5 July 2011 training expires on the 1st of August 2012 (essentially one year with a grace period to the first day of the next month). Most of the training is completed within month of the expiry date. In this case, most training would occur in July 2012...I can enter the date of training...say July 20, 2012 and the 'training due' sheet will then read Aug 1 2013)

The problems comes with an allowance in the regulations that govern us which allows for training done within 90 days of the expiry date will extend the valid period for a year....to continue the example (Training due to expire 1 Aug 2012...I can write an exam on June 3, 2012 and my valid date is then 1 Aug 2013. The problem is that I want the Training date sheet to read the date of training (June 3 2012)...of course when I enter this...it changes the training due date to July 1 2013)

I'm wondering if there is someway to code training that is done early ie) June 3, 2012 (e2), standing for 2 months early and have the 'training due' sheet look for codes in the dates and if it finds them to apply a different formula for calculating the 'training due date'

The problem i'm finding is that I can't add anything to the dates without returning #no value#

Not sure if that is a confusing explanation or not...it's clear in my head...but it's my problem

Cheers

 

[AD_Taylor]

I'd add the months early bit as a separate column, and then in the formula that works out the next date/expiry date, you could add that on in terms of months?

I would be able to give more if I had Excel in front of me to test the formula I want to give you.

 

[Talbot123]

=IF('All Pilots Actual Dates'!G25="",(""),DATE(YEAR('All Pilots Actual Dates'!G25)+1,MONTH('All Pilots Actual Dates'!G25)+1,1))

so that is the formula on the 'training due' sheet...

here if there is nothing in the cell, it displays nothing and is conditionally formatted to display green. if there is a date there, it rounds it up to the first day of the next month..one year from the date.

so if i understand your suggestion...have the column beside the training designated for an early indication...a 2 for 2 months early for example.

Then in the formula that is above, there would be someway to look for that indication and if it was there, use a different formula and if it wasn't there.....it would use the formula as indicated?

 

[AD_Taylor]

Yes - in the formula you posted you could use something along the lines of:

=IF('All Pilots Actual Dates'!G25="",(""),DATE(YEAR('All Pilots Actual Dates'!G25)+1,MONTH('All Pilots Actual Dates'!G25)+1 + IF('All Pilots Actual Dates'!H25 = "", 0, 'All Pilots Actual Dates'!H25) ,1))

Assuming that the months early column is in column H. This will then add 0 to the months if the cell is blank and add the corresponding amount if the cell has a value.

Hope this helps!

 

[Talbot123]

brilliant...I'll check it out...

 

[Talbot123]

My spread sheet is a master piece...thank you soooo much!!

 

[AD_Taylor]

No problem glad I could help.