Need help with Date Calculation

Options

I am just learning about date formulas in Smartsheet and having trouble -

Need help with "ExpirationDate", 1st Contact, and 2nd Contact formulas

Answers

  • Christian G.
    Christian G. ✭✭✭✭✭✭
    Options

    You need to construct the date with "Date(Year,Mouth, day)"

    So we extract the Year form POA Signed with Year(), month with month() and Day with day()

    We add 6 months to the month. But we need to check if it push it over 12, to change the year also.

    ExpirationDate

    =if(month([POA Signed]@row)<=12,Date(Year([POA Signed]@row),Month([POA Signed]@row)+6,Day([POA Signed]@row),Date(Year([POA Signed]@row)+1,mod(Month([POA Signed]@row)+6,12)+1,Day([POA Signed]@row)

    1st Contact

    =[ExpirationDate]@row-30

    2nd Contact

    =[ExpirationDate]@row-14

  • amy pelayo
    amy pelayo ✭✭✭✭
    Options

    Hi Christian, I put in the formula and I am getting an #incorrect argument set error - not sure what to do? Any ideas? Regards, Amy

  • Christian G.
    Christian G. ✭✭✭✭✭✭
    Options

    My bad

    here is a corrected formula

    =IF(MONTH([POA Signed]@row) + [Months POA Valid]@row <= 12, DATE(YEAR([POA Signed]@row), MONTH([POA Signed]@row) + [Months POA Valid]@row, DAY([POA Signed]@row)), DATE(YEAR([POA Signed]@row) + 1, MOD(MONTH([POA Signed]@row) + [Months POA Valid]@row, 12), DAY([POA Signed]@row)))


    Keep in mind that the value in "Months POa Valid" should not exceed 12, otherwise it gives a wrong date. If you need to put something higher then 12, I can ajust the formula.

  • amy pelayo
    amy pelayo ✭✭✭✭
    Options

    Hi Christian, I am getting an unparseable error.. Any ideas why? - Regards, Amy

  • amy pelayo
    amy pelayo ✭✭✭✭
    Options

    I figured it out !!! Thank you so much for your help !!!!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!