Help with formula

Amandeep kaur

Can someone look at this formula, its working for VSS but not for CPIC. It just stopped working couple of months ago

=IF([Type of PRC:]@row = "VSS", DATE(YEAR([PRC issue date]@row) + 3, MONTH([PRC issue date]@row), DAY([PRC issue date]@row)), IF([Type of PRC:]@row = "CPIC", DATE(YEAR([Start Date]@row), MONTH([Start Date]@row) + 4, DAY([Start Date]@row))))

Error says Invalid value

Amandeep kaur

thanks so much, made a little change to the highlighted part and then it worked

=IF([Type of PRC:]@row = "VSS", DATE(YEAR([PRC issue date]@row) + 3, MONTH([PRC issue date]@row), DAY([PRC issue date]@row)), IF([Type of PRC:]@row = "CPIC", IF(MONTH([Start Date]@row) < 9, DATE(YEAR([Start Date]@row), MONTH([Start Date]@row) + 4, DAY([Start Date]@row)), DATE(YEAR([Start Date]@row) + 1, MONTH([Start Date]@row) - 8, DAY([Start Date]@row)))))

Adam Murphy

I would guess it is because when you add 4 to the month in the start date it errors out if that results in > 12. You will need to add logic to handle the rollover for the year. Something like this should work:

=IF([Type of PRC:]@row = "VSS", DATE(YEAR([PRC issue date]@row) + 3, MONTH([PRC issue date]@row), DAY([PRC issue date]@row)), IF([Type of PRC:]@row = "CPIC", IF(MONTH([Start Date]@row) < 9, DATE(YEAR([Start Date]@row), MONTH([Start Date]@row) + 4, DAY([Start Date]@row)), DATE(YEAR([Start Date]@row + 1), MONTH([Start Date]@row) - 8, DAY([Start Date]@row))))

Amandeep kaur

thanks so much, made a little change to the highlighted part and then it worked

=IF([Type of PRC:]@row = "VSS", DATE(YEAR([PRC issue date]@row) + 3, MONTH([PRC issue date]@row), DAY([PRC issue date]@row)), IF([Type of PRC:]@row = "CPIC", IF(MONTH([Start Date]@row) < 9, DATE(YEAR([Start Date]@row), MONTH([Start Date]@row) + 4, DAY([Start Date]@row)), DATE(YEAR([Start Date]@row) + 1, MONTH([Start Date]@row) - 8, DAY([Start Date]@row)))))