I'm using Google sheets and the
MAX() function works only on dates with format
mm/dd/yyyy. How to get the
MAX() of date with format
When using Excel (or Sheets) it tries to parse strings into dates, but can be a bit hit or miss (more miss) when you use a format different to what the machine you are entering it on is set to e.g. yyyy/mm/dd vs dd/mm/yyyy or dd/mm/yyyy vs mm/dd/yyyy.
Firstly, to check if a date was correctly parsed, try adding a day to it. in your example, in B2 enter
=A2+1 and you should get an incremented date. If you get an error it means it's a string, not a date. To use the dates, you need to parse it yourself. In your specific example try next to your original date column
=DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2)) and use that for subsequent calculations.