Nested If(IsBlank or If(IsDate Formula

Shelly Toy

I am trying to write a formula that returns a date value in the "Actual Date" column, based on the following scenarios...

1. if there is a date in the "Revised Date" column, then return that date in the "Actual Date" column
2. If the "Revised Date" column is blank, and there is a date in the "Forecasted Date" column, then return that date in the "Actual Date" column
3. If the "Forecasted Date" and "Revised Date" columns are blank, then return the "Original Date" in the "Actual Date" column

Note the dates in the "Original Date", 'Forecasted Date" and "Revised Date" columns are indexed in from another sheet.

Jeff Reisman

@Shelly Toy

We will need to nest some IF, but because of your criteria and the way IFs are evaluated, we won't need any ANDs or ISBLANKs:

=IF(ISDATE([Revised Date]@row), [Revised Date]@row, IF(ISDATE([Forecasted Date]@row), [Forecasted Date]@row, IF(ISDATE([Original Date]@row), [Original Date]@row)))

In Smartsheet, IFs are evaluated from left to right. So it starts with "Is the Revised Date a date value?" If so, set the value to [Revised Date]@row. If the Revised Date is not a date value, the system goes to the false condition, which is "Is the Forecasted Date a date value?" If so, set the value to the [Forecasted Date]@row. If it's not a date value, the go to the false condition, which is "Is the Orginal Date a date value?" If so, set the value to the [Original Date]@row. If [Original Date]@row is not a date value, don't set any value at all.

Jeff Reisman

@Shelly Toy

We will need to nest some IF, but because of your criteria and the way IFs are evaluated, we won't need any ANDs or ISBLANKs:

=IF(ISDATE([Revised Date]@row), [Revised Date]@row, IF(ISDATE([Forecasted Date]@row), [Forecasted Date]@row, IF(ISDATE([Original Date]@row), [Original Date]@row)))

In Smartsheet, IFs are evaluated from left to right. So it starts with "Is the Revised Date a date value?" If so, set the value to [Revised Date]@row. If the Revised Date is not a date value, the system goes to the false condition, which is "Is the Forecasted Date a date value?" If so, set the value to the [Forecasted Date]@row. If it's not a date value, the go to the false condition, which is "Is the Orginal Date a date value?" If so, set the value to the [Original Date]@row. If [Original Date]@row is not a date value, don't set any value at all.