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]] )
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.
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.
- 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.
