Prioritize by available date

Jlilly

I have a sheet that has multiple dates on a row, but not all will be known at the same time. I’m looking for a function or a way to provide the current best estimated date by priority. Basically, if there is a date in this column, use it, if not, use this other column, and if nothing is in either, use the date from this other column. I’ve been trying to work on this with an If function, but I’ve gotten stuck at only one level of priority. The below formula in my “Date for Chart” column has gotten me to get “actual arrival date” if “invoiced date” is empty. But I’m getting stuck trying to get the result if they are both empty to choose the next level of priority, and so on. Do you have any advice for this?

=IF(NOT(ISBLANK([INVOICED DATE]@row)), [INVOICED DATE]@row, [ACTUAL ARRIVAL (DELIVERY) DATE]@row)

Paul Newcome

You would use a nested IF where IF #2 goes in the 3rd portion of IF #1, IF #3 goes in the 3rd portion of IF #2, so on and so forth.

=IF([This Date]@row <> "", [This Date]@row, IF([That Date]@row <> "", [That Date]@row, IF([Another Date]@row <> "", [Another Date]@row, .................................))))

Paul Newcome

You would use a nested IF where IF #2 goes in the 3rd portion of IF #1, IF #3 goes in the 3rd portion of IF #2, so on and so forth.

=IF([This Date]@row <> "", [This Date]@row, IF([That Date]@row <> "", [That Date]@row, IF([Another Date]@row <> "", [Another Date]@row, .................................))))