Forum Discussion
IF Statements on variable dates
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]] )
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.
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.