Calculate Maximum date excluding a specific date

Brass Contributor

I needed assistance with calculating the largest or oldest date in two different columns. I want the date 12/31/9999 to be excluded, as it is a date that populates due to a formula in the cells. 

Thank you!

=MAX('Resident Line List'!AK:AK,'Staff Line List'!AK:AK)

4 Replies


You mention minimum but your formula uses MAX

Which is correct?

@Hans Vogelaar Sorry meant largest or "oldest" date


largest or "oldest" is ambiguous too. Do you want the earliest date or the latest date?


Assuming the dates are not more than 150 years in the future


= MAXIFS(dates, dates, "<99999")​