Multiple Criteria for 1 Line in a Case Statement
I have a series of Case statements that are used to calculate a due date, based on a large number of contract terms (ex. 6 mos end of FW / 24 mos 1st pymt, 6 mos end of FW / 30 mos from 1st pymt). There are two large case statement formulas to calculate the two side of the terms, and then another formula that returns the earlier of the two dates.
My problem is that sometimes those terms are based on another value that will return what we refer to as a Null Date (ex. 01-01-0001), so I want to be able to put a qualifier to the individual lines in the case statement. For that term, the logic would be that if the "other term" equals Null Date, then return "1st calculation," otherwise use "2nd calculation."
This is an abbreviated version of the Case Statement:
Case ([Report Delivery Window - MAI] , "8 weeks from end of FW / no later than 12 weeks from end of FW" , [Fieldwork End Date (AUD)] + Weeks(12) ,
"90 days from location exit / 90 days from end of FW" , [Fieldwork End Date (AUD)] + Days(90) ,
"90 days from end of FW / 6 mos from start of FW" , AdjustMonth([Fieldwork Begin Date (AUD)] , 6) ,
"90 days from the end of FW / 6 mos from end of FW" , [Fieldwork End Date (AUD)] + Days(180) ,
"90 days from end of FW / 30 days before MSO Deadline" , [MSO Report Deadline] - Days(30) ,
"4 mo from end of FW/no later than 1 year from start" , AdjustMonth([Fieldwork Begin Date (AUD)] , 12) ,
"6 mo from beginning/no later than 6 mo end" , AdjustMonth([Fieldwork End Date (AUD)] , 6) ,
"6 mo from beginning/no later than 3 mo end" , AdjustMonth([Fieldwork End Date (AUD)] , 3) )
The term in question is "90 days from end of FW / 30 days before MSO Deadline." I'm not sure how to write the extra formula within the formula.
------------------------------
Jennifer Peterson
------------------------------
My problem is that sometimes those terms are based on another value that will return what we refer to as a Null Date (ex. 01-01-0001), so I want to be able to put a qualifier to the individual lines in the case statement. For that term, the logic would be that if the "other term" equals Null Date, then return "1st calculation," otherwise use "2nd calculation."
This is an abbreviated version of the Case Statement:
Case ([Report Delivery Window - MAI] , "8 weeks from end of FW / no later than 12 weeks from end of FW" , [Fieldwork End Date (AUD)] + Weeks(12) ,
"90 days from location exit / 90 days from end of FW" , [Fieldwork End Date (AUD)] + Days(90) ,
"90 days from end of FW / 6 mos from start of FW" , AdjustMonth([Fieldwork Begin Date (AUD)] , 6) ,
"90 days from the end of FW / 6 mos from end of FW" , [Fieldwork End Date (AUD)] + Days(180) ,
"90 days from end of FW / 30 days before MSO Deadline" , [MSO Report Deadline] - Days(30) ,
"4 mo from end of FW/no later than 1 year from start" , AdjustMonth([Fieldwork Begin Date (AUD)] , 12) ,
"6 mo from beginning/no later than 6 mo end" , AdjustMonth([Fieldwork End Date (AUD)] , 6) ,
"6 mo from beginning/no later than 3 mo end" , AdjustMonth([Fieldwork End Date (AUD)] , 3) )
The term in question is "90 days from end of FW / 30 days before MSO Deadline." I'm not sure how to write the extra formula within the formula.
------------------------------
Jennifer Peterson
------------------------------
