Adding days to a Date to determine a future date

Marlayna Sullivan

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!

Jeff Reisman

@Marlayna Sullivan

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)

Marlayna Sullivan

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?

Jeff Reisman

https://community.smartsheet.com/discussion/comment/328847#Comment_328847

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.

Genevieve P.

Hi @Marlayna Sullivan

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