Date Formula Assist

John Stanik

Hi,

I have a secondary date column (called "Review Date") that populates a date one year from an existing date column. I'm using a helper column called "AddYear" which will always display a 1 based on a new row entry. This formula is working to give me the new date (one year later) in the new column:

=DATE(YEAR([Date Column]@row) + AddYear@row, MONTH([Date Column]@row), DAY([Date Column]@row))

I'm also trying to incorporate this logic (to keep the new date column blank if the original date column is blank):

=IF([Date Column]@row = "", "")

Any ideas on how to combine these two formulas to give me the future date if a date is available in "Date Column" and keep the future date column blank if the "Date Column" has not been populated?

Thanks,

John

Jeff Reisman

You can probably do away with the helper column with a change to your formula. Using an IF statement around the formula will let you set the value to blank if there's nothing in the Date Column:

=IF(ISBLANK([Date Column]@row), "", DATE(YEAR([Date Column]@row)+1, MONTH([Date Column]@row), DAY([Date Column]@row))

The logic is: If the Date Column is blank, set the value to null (""), if it's not blank, create a Date from the Date Column year + 1, the Date Column month, and the Date Column day.

Jeff Reisman

You can probably do away with the helper column with a change to your formula. Using an IF statement around the formula will let you set the value to blank if there's nothing in the Date Column:

=IF(ISBLANK([Date Column]@row), "", DATE(YEAR([Date Column]@row)+1, MONTH([Date Column]@row), DAY([Date Column]@row))

The logic is: If the Date Column is blank, set the value to null (""), if it's not blank, create a Date from the Date Column year + 1, the Date Column month, and the Date Column day.

John Stanik

@Jeff Reisman

That was perfect! Thanks for the help and the speedy reply!

Best,

John