Nested ISDATE formula

mitch.steele100316

Hello,

I have several date columns that track the scheduled release dates of a product in various territories.  I'd like to have a formula in a field that looks at each of the columns that have dates and returns a string that allows for a quick recap of the territories for which there is a scheduled release.

Example:  If there is are release dates for United States, Canada, Mexico and United Kingdom, but not for France, Germany, or Sweden,  in another field return "US/CAN/MEX/UK".

I can get one date field to return with:

=IF(ISDATE([MEXICO RELEASE DATE]1), "MEX")

Not sure how to get additional results for multiple date fields.

Appreciate any guidance.



Thank you.

Paul Newcome

The most efficient way I can think to do this would be to create an extra row. In this example I will say it is in row 1, but you can use whatever row you want.

 

In this new row, you would list out the country abbreviations in each of the columns.

 

You can then use a JOIN/COLLECT to pull together each of the values in row 1 based on which columns in the current row have dates in them. It would look something like this...

 

=JOIN(COLLECT([First Column]$1:[Last Column]$1, [First Column]@row:[Last Column]@row, ISDATE(@cell)), "/")

.

NOTE: The $ used to lock in the row reference. This portion should be pointing at whatever row you have the country abbreviations in.