Prioritize by available date
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)
Best Answer

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, .................................))))
Answers

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, .................................))))
Help Article Resources
Categories
Check out the Formula Handbook template!