# Excel MAX IF Function To Ingore N/A Error And Zero Date (1/0/00)

by DomainsFeatured   Last Updated April 24, 2018 02:01 AM

I have the following data. I need to get the most recent date. However, I'm unable to exclude both the Error and 1/0/00 at the same time.

I have tried using `=MAX(IF(NOT(ISERROR(B16:E16)),B16:E16))` but that returns 1/0/00 as the MAX.

Also, I have tried `=MAX(IF(B15:E15>1/0/0,B15:E15))` to get rid of the zero but that doesn't quite work either. Also, when I combine the two, it really doesn't work.

In the example below, the output for row 2 under "Date-Max" would be 7/28/16.

``````Date1   Date2   Date3   Date4   Date-Max
7/26/16 #N/A    #N/A    1/0/00  Output
7/20/16 7/28/16 1/0/00  1/0/00  Output
7/25/16 #N/A    #N/A    1/0/00  Output
7/25/16 #N/A    #N/A    1/0/00  Output
7/20/16 #N/A    #N/A    1/0/00  Output
7/25/16 7/29/16 #N/A    1/0/00  Output
7/17/16 #N/A    8/1/16  1/0/00  Output
``````

Need the max output for each row.

Tags :

Consider data in A1 through A10, use this array formula:

``````=MAX(IF(NOT(ISNA(A1:A10)),A1:A10,0))
``````

It is not necessary to test for zero, since MAX() will pick any positive value over zero anyway.

Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key.

Gary's Student
August 03, 2016 19:46 PM