Adding days to a Date to determine a future date
I am trying to create a formula that will look at the date within the row, look at the frequency and then determine what the next date will be. I am using the following formula:
=[Estimated Date of Charge]@row + [Frequency in Days]@row
Here is the result:
The system is just populating the date in the Estimated Date of Charge field (which is set as a Date field) and adding the Frequency in Days (which is a Text field) to the end.
Also (not sure if it matters), the formula in the Frequency in Days column is: =IFERROR(IF([Frequency of charge]@row = "12 months", "365", IF([Frequency of charge]@row = "6 months", "180", IF([Frequency of charge]@row = "3 months", "90", IF([Frequency of charge]@row = "1 month", "30")))), " ")
By the end of this, I want to set an automation that will be triggered by the Reoccurring Date field to send a notification to a user to confirm that the cost is still applicable.
Any help is greatly appreciated!
Answers

Wrap your Frequency in Days value in the VALUE function. Notice how the 365 is on the left side of the cell? That's likely because Smartsheet is storing it as text based on how your formula is producing the value. Smartsheet can't do math on text, so numeric text values get appended to numeric values such as dates. The VALUE function converts it back to it's numeric value so that it can be added to the date. As long as the Estimated Date of Charge is an actual date value in a date field, and Reoccuring Date is also a date field, this will work:
=[Estimated Date of Charge]@row + VALUE([Frequency in Days]@row)
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Thank you @Jeff Reisman for the reminder on VALUE. I tend to forget that function however, that didn't seem to solve this issue. Now I am getting #INVALID COLUMN VALUE.
I also reconfirmed that the Estimate Date... field is a date column. Do you have any other thoughts on why this is not playing nice?

Sorry for the delay  I've been down with Covid.
Double check that the Reoccuring Date field is a date type. When I put =DateField@row + 365 into a date column, I get a date one year in the future. When I put it into a Text/number column, I get the #INVALID COLUMN VALUE error.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

I see you marked Jeff's answer as not resolving the issue. Would you be able to clarify how this hasn't helped?
I agree that the Reoccurring Date column would need to be set as a Date type of column (even though you're typing text as a formula into it in order for this formula to work. Can you confirm it's set as the right type of column, and that you're using the VALUE() function that Jeff suggested in his first comment?
Thanks!
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
Help Article Resources
Categories
Check out the Formula Handbook template!