Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
LIVE

Return the newest date from 2 date columns, using index/match

Copper Contributor

I am running excel 2016, and trying to figure out how I can do a match to the zone name under zone column to the date and date2 columns, and return the zone first that has the oldest date, a blank in both date columns would be the oldest. So if both date columns are empty return those zone names first, then return zones based on oldest to newest date checking both date and date2.

 

Thank you,

Glen

 

 

 

Here is a sample sheet for it.

 

Thank you,

Glen

1 Reply

@Satcom9 Bearing in mind that the table in your file is called "Table36", add a column that returns zero or the sequential number of the oldest of DATE and DATE2.

=IF(COUNT(Table36[@[DATE]:[DATE2]])=0,0,MIN(Table36[@[DATE]:[DATE2]]))

 Custom sort on this added column first. And I resume you would then want to sort by Zone code.

 

In case you would ever want to sort the table back into its original order, add an index column with the row numbers 1 to 139 before sorting.