Multiple IFS Formula to return a date value

Lisa Matthews

Hello,

I need some help, please, with a formula to return a date value based on multiple conditions:

Return value in [Confirmed Install Date]:

1)   If [Actual Install Date] is NOT blank, return this value to [Confirmed Install Date]. Stop.

2)   If [Actual Install Date] is blank and If [Tech Dispatch Date1] is NOT blank, return this value to [Confirmed Install Date]

3)   If [Actual Install Date] and [Tech Dispatch Date1] are both blank, then return the value in [Est. Install Date] to [Confirmed Install Date]

4)   If [Actual Install Date], [Tech Dispatch Date1] and [Est. Install Date] are blank, then [Confirmed Install Date] should be blank

Thank you so much for your help!

--Lisa M.

Davidp

https://community.smartsheet.com/discussion/102482/multiple-ifs-formula-to-return-a-date-value

1. Give this a try:

=IF(NOT(ISBLANK([Actual Install Date]@row)), [Actual Install Date]@row, IF(NOT(ISBLANK([Tech Dispatch Date1]@row)), [Tech Dispatch Date1]@row, IF(NOT(ISBLANK([Tech Dispatch Date1]@row)), [Tech Dispatch Date1]@row, [Est. Install Date]@row)))

If Est.Install date, Tech Dispatch Date1, and Actual Install Date are all filled in, confirmed date will use Actual install date.

If Actual Install Date is blank, the tech dispatch Date1 will fill in the confirmed install Date. If both actual install date and techdistpatch date1 are blank then it will populate the est.install date.

If all three right fields are blank then the confirmed install date will be blank.

Ultimately based on what I am reading from your attempt is you want the right most valid data record to reference the "confirmed Install Date".

Kelly Moore

Hey @Lisa Matthews

Try this.

=IF(ISDATE([Actual Install Date]@row), [Actual Install Date]@row, IF(ISDATE([Tech Dispatch Date1]@row), [Tech Dispatch Date1]@row, IF(ISDATE([Est. Install Date]@row), [Est. Install Date]@row)))

Will this work for you?

Kelly