What formula will return one of three date fields based on set critera?

LSW

Okay, so here's another one for the community

We have a tracker with three date fields. The PM Received date #1 should take priority and be returned if not blank, otherwise, we want one of the other two dates, whichever is not blank. (and only one of #2 & #3 will be not blank)

1. PM Received Intake Date: The actual date project was received
2. Engagement Date: Targeted engagement date for one set of project types
3. Kickoff Month: A second possible target date for a different project type

What we tried and got a #UNPARSEABLE error…

=IF(ISBLANK([PM Received Intake Date]@row), IFERROR(INDEX(COLLECT({Engagement Date}, { Engagement Date},NOT(ISBLANK([Engagement Date]@row)),{Kickoff Month},{Kickoff Month},NOT(ISBLANK([Kickoff Month]@row)))),PM Received Intake Date]@row))

Kerry St. Thomas

THIS SOLUTION WILL ONLY APPLY IF YOU CAN GUARANTEE THAT #2 OR #3 WILL ALWAYS BE BLANK.

With the giant caveat above in place, you can use JOIN to make things a bit easier. Using JOIN is going to basically flatten your two columns into one - and since one is always blank the end result is a single date. JOIN does have a quirk: the columns need to be next to each other. So if that structure isn't part of what you can control, you can do an alternate basically using Col2@row + Col3@row logic in the formula.

=IF(ISDATE([PM Received Intake Date]@row),
[PM Received Intake Date]@row,
JOIN([Engagement Date]@row:[Kickoff Month]@row))

Good luck!

LSW

Hi Kerry, Unfortunately, it is likely two out of three of the dates will always have a date. Two are estimated dates (#2/3) and one is the actual start date (all feeding from other source sheets).