max(filter()) giving an error for date range in sheets

I am trying to find the date that a student had their top score. I find the top score using maxifs. When I trying to find the data I have tried using maxifs or filter and the max on the filter and I still get the same error.

One of my students (B A) had the same results on 2 different days. (10/29/2021 and 10/15/2021). Filter returns these two dates. I do an isdate() on both and get a true. But when I do a max on the results I get a 0.

I think I created a link to the data: https://docs.google.com/spreadsheets...it?usp=sharing

If you looking the results tab, student B A had the same top score (15.03) on 10/29/2021 and 10/15/2021. When I do a Max on the the filter it returns 0.

Why does the max cause the value to be 0?

If I do a maxifs I get the same results. I think it has something to do with do max on a date but I don't know why it isn't working. Everything I read says Max or Maxifs should work. If I do an index on the filter =INDEX(FILTER(data!B:B,data!A:A=A6,data!F:F=B6),1) it does work, but to me index isn't as logical as Max and I want to know why Max doesn't work.

Originally Posted by janmorris

the problem is that you are not using a consistent date format.

"data" column B must be formatted as a Date, and so too should "results" column C

I suspected this, but when I did the =isdate() I got a true. How can I format the data to it is a date?

i already changed the format from the menu, see the image for where to change format style/type

Screen Shot 2021-11-24 at 10.26.50 pm.png

That worked. Thank you!! Is there a way so I do not have to do this every time I import data? or a way to do this with a command inside of the filter function? Worst case it will add one more step to my daily import.

Thank you again!!

if importing from another sheet with IMPORTRANGE, then you should format the original data as date.

if you are copy/pasting then you can try "Paste special" > "Paste as values"