Date Formula Assist

Options

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

Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    Options

    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.

    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!

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    Options

    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.

    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!

  • John Stanik
    Options

    @Jeff Reisman

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

    Best,

    John

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!