I am trying to build a formula to calculate expiry date based on conditionality.

So, if someone fills out the type of PRC as 'VSC', formula should add 'PRC issue date +3 years' to get PRC Expiry date,

another condition is, if staff fills out type of PRC as 'CRIC/CIPIC', the formula should add PRC issue date +6 months

=IF([PRC Type]@row = "VSC", DATE(YEAR([PRC Issue Date]@row) + 3, MONTH([PRC Issue Date]@row), DAY([PRC Issue Date]@row)), DATE(YEAR([PRC Issue Date]@row), MONTH([PRC Issue Date]@row) + 6, DAY([PRC Issue Date]@row)))

Does this look good or we can perfect it.

Best Answer

  • DKazatsky2
    DKazatsky2 ✭✭✭✭✭
    Answer ✓

    Hi @Amandeep kaur,

    Try this.

    =IF([Type of PRC]@row = "VSC", DATE(YEAR([PRC Issue Date]@row) + 3, MONTH([PRC Issue Date]@row), DAY([PRC Issue Date]@row)), IF([Type of PRC]@row = "CRC/CIPIC", DATE(YEAR([PRC Issue Date]@row), MONTH([PRC Issue Date]@row) + 6, DAY([PRC Issue Date]@row))))

    Hope that helps,

    Dave

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!