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 re-confirmed 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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!