Reverse Date Calculation

Mark Proulx

Hi,

I have a formula which uses criteria from a drop-down (Program) and the date in another column (Drop) to populate a secondary date field (Brief) automatically. The issue is when Drop is a different year than the current, I get INVALID DATA ENTRY errors. Here is the formula and screenshots:

=IF(Program@row = "Cultivation", DATE(YEAR(Drop@row) - INT((12 - MONTH(Drop@row) + 6) / 12), MOD(MONTH(Drop@row) - 6 - 1, 12) + 1, DAY(Drop@row)), IF(Program@row = "Acquisition", DATE(YEAR(Drop@row) - INT((12 - MONTH(Drop@row) + 7) / 12), MOD(MONTH(Drop@row) - 7 - 1, 12) + 1, DAY(Drop@row)), IF(OR(Program@row = "Engagement", Program@row = "Stewardship"), DATE(YEAR(Drop@row) - INT((12 - MONTH(Drop@row) + 3.5) / 12), MOD(MONTH(Drop@row) - 3.5 - 1, 12) + 1, DAY(Drop@row)), "")))