Formula to keep Date when another date column is blank

Melanie Sanders

Hi,

I am trying to create a formula that will keep the existing date in the cell if another column is blank and if there is a date then I want to add 365 days to the other column and return it in my NEXT CONTACT DATE cell

=IF(TODAY@row, "", [NEXT CON TACT DATE]@row, IF(TODAY@row, TODAY@row + 365))

My Column Headers are:

TODAY (populates when an attachment is added or remains blank)

NEXT CON TACT DATE (currently has a date in it but would like it to auto update based on the date added to the TODAY column)

Any help is greatly appreciated as always.

Genevieve P.

Hi @Melanie Sanders

The formula would need to be in its own column. You'll get an error in the "NEXT CON TACT DATE" column because you're referencing that same cell in the formula:  [NEXT CON TACT DATE]@row

For the Invalid Value error, what type of column did you create? This would need to be a Date Type of column in order to return a Date value. Can you check the third column to make sure it's a Date column? (Double click on the title of the column).

Another thing to check is to ensure your  NEXT CON TACT DATE column is indeed spelled like this with an extra space between the N and T in Contact. The formula will need to have the exact column name spelled out, including spaces.

Let me know if this works, now!

Cheers,

Genevieve

Genevieve P.

Hi @Melanie Sanders

With an IF statement, you'll want to state the logic before putting a comma, like so:

=IF(TODAY@row = "", 

Then you can state what you want it to do if that's true. Think of the comma as "Then Return":

=IF(TODAY@row = "", [NEXT CON TACT DATE]@row,

But if that isn't true, the next comma represents "otherwise", and we can state what we want it to do if the cell is NOT blank:

=IF(TODAY@row = "", [NEXT CON TACT DATE]@row, TODAY@row + 365)

However, keep in mind that this would need to be built in a third, separate column to either your TODAY column or your NEXT CON TACT DATE column, since you're looking to return either of these data points. Does that make sense?

Let me know if this works for you! If not, it would be helpful to see a screen capture of your sheet, but please block out sensitive data.

Cheers,

Genevieve

Melanie Sanders

@Genevieve P.

Thank you for your response. I did copy and paste your formula into my sheet and received a circular reference error when I pasted it into my NEXT CON TACT DATE cell. When I pasted it in a third column I also received an error invalid column value. Screenshot is below.

Thank you!

Genevieve P.

Hi @Melanie Sanders

The formula would need to be in its own column. You'll get an error in the "NEXT CON TACT DATE" column because you're referencing that same cell in the formula:  [NEXT CON TACT DATE]@row

For the Invalid Value error, what type of column did you create? This would need to be a Date Type of column in order to return a Date value. Can you check the third column to make sure it's a Date column? (Double click on the title of the column).

Another thing to check is to ensure your  NEXT CON TACT DATE column is indeed spelled like this with an extra space between the N and T in Contact. The formula will need to have the exact column name spelled out, including spaces.

Let me know if this works, now!

Cheers,

Genevieve