IF Statements on variable dates

[TheLimbix]

Hi Everyone,

The Question;

I am looking for help in constructing an IF statement or another formula to auto-generate Expiry dates based on variable rules.

A Little 'O Context;

This information is used as part of a small database that permits marine traffic through a controlled area. The data is mostly input by myself, however, there are some other users in my organisation and a number of people who read the output information.

The data needs to be precise as it is used by decisions makers in my organisation and is sometimes made available to outside parties. Further, this will reduce the human element in the calculation, therefore, reducing the potential for error and promotes better reporting down the line.

The Data;

• Column B - The date a vessel was inspected.
• Column C - The date of painting (for the antifouling paint of vessel hulls, it has a time frame of effectiveness). This date is colour coded using Conditional Formatting being a visual representation of Column D.
• Column D - This is a column to identify the approx. age of the antifoul. It is identified through an IF statement.
• Column E - The inspection is valid for different times based on the age of the paint, brand, use and coat thickness. The inspection becomes invalid if the paint reaches its term of effectiveness. This data is expressed in the number of months the paint is effective.
• Column F - Lastly, this date represents the end of effectiveness for the paint. This date is calc'd using an EDATE formula.

The key time frames of the age of paint are

• 0-3 months, inspection lasts for 6 months (not that much different from 6-12 months but has key policies around it that are different to the next category, 
• 6-12 months, inspection lasts for 6 months but not longer than 12 months from the date in Column C (original painting).
• Beyond 12 Months, inspection lasts 3 months and is invalid after the date in Column F is reached.

Guys, I tried and I Googled;

I started this task a few days ago. I have started, come close but not quite right and sometimes completely failed. I have gone to google and found solutions that look close but do not work. I have focused on IF statements and expressions, however, have tried some other formulae that may work with no luck.

Extra Info;

I have attached an example spreadsheet as the other information on the original is commercially sensitive. In columns J to L, I have included what the dates should be based on the rules as set above.

Many thanks for your help;

I would appreciate any help. I am continuing to work on this and if I find the solution I will post for the completion and future 'Googlers'. 

[#2]

You've given a lot of helpful info, but I'm still not quite understanding the whole. Maybe some of the other Excel experts can decipher all of this; I'm going to need to ask a few more questions to make sure I'm tracking with you. It's what I'd do if we were sitting down face-to-face.

I gather that it's column G where you want this final formula. (Please confirm)
I get that column J is the desired answer, i.e., what should appear as a result of whatever formula is created in G
What I don't get yet is how each rule applies in each row. I.e., what in columns B through F tells me which condition is to be met.

It will help -- this is all very clear in your mind, I know -- it will help if you walk through maybe the first three rows, describing (in words) HOW data in columns B through F lead to the answer in column J....from those verbal descriptions, then, our challenge is to create a single conditional formula that consistently produces the desired result.

[#3]

Thanks @mathetes that is a good point, it is clear in my mind. I'll do my best to describe this in words.

Column A is a starting point so to speak. I am given a form with a date on it, that is when the date of  inspection and when the valid period begins.
On that same form is a date which is put into Column C. This is the date that sets the conditions for how long the inspection will be valid for.
Column D is the current age of the antifoul based on the date difference between TODAY() and Column C. This is the main parameter that sets the conditions for the inspection expiry. If the Date Dif between TODAY and Column C is;

• Within 3 Months the Inspection is valid for 6 months,
• Within 6 Months the Inspection is valid for 6 months or until the date in Column F (Calc'd from Col' C and Col' E. Info for Col' E is also collected from the inspection form) reaches 12 months old, which ever comes first.
• Beyond 12 Months until the date in Column F is reached the inspection lasts for 3 months.

Column F is calc'd using the date supplied for Col' C and Col' E. In some cases this will be what sets the upper limit of the inspection.

The info I supplied in Column L, with Col' J being the desired output, is the months the reason why the expiry date is what it is.

[#4]

You basically repeated your first post. I was looking for step by step through the first three examples, how you got the answer in col J, which means, I guess, how you got the info in column L.

I'm asking you to be VERY basic, explain step by step, what leads to the right answer, for those examples you give in rows 2, 3 and 4. What you've given is the higher level rules, but you've not "applied them" visibly, by which I mean, so that all I (or any other person here) needs to do is translate your words for rows 2, 3 and 4.

[#5 · Accepted solution]

@TheLimbix 

Does this do it?

I have converted to a table to keep the data and formulas in sync.

= MIN(
   EDATE([@[Inspection Date]], IF(today-[@[AntiFoul Application Date]] < 182, 6, 3)),
   [@[Antifoul Expiry]] )