Need help with Date Calculation

amy pelayo

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

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

Christian G.

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

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.

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

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

amy pelayo

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

amy pelayo