Excel MAX IF Function To Ingore Zero Date (1/0/00)

Question

=MAX(IF('[YTD -2021.xlsx]OB'!$H$2:$H$23967=$C5,IF('[YTD -2021.xlsx]OB'!$J$2:$J$23967=$B$1,'[YTD -2021.xlsx]OB'!$A$2:$A$23967)))

I want to ignore Zero date and result should give me 0 or blank instead of 0/01/1900.

Answer 1

1. Add a new column with formula if(date=0,"",date)
2. Do the max on the new column
3. (optional) hide one of the columns (maybe the old one) for aesthetics

Q: Why is bothering you in the first place? 0 is only the max-date if there are no dates?
MAX(01/01/1900, 16/02/2021) = MAX(0, 16/02/2021) = 16/02/21

Answer 2

The question is actually simply asking how to have a 0 or a blank if the result of his calculation is 0. As opposed to the resulting 0 being shown as a date of 0. He is happy with his method of achieving a result, just wants a different display if it is 0.

For that, you need do nothing more than change the formatting for the result cells. Currently, it is presumably some date format. Excel allows regular number formatting to have formatting set for as many as four kinds of results for each cell:

1. Positive values
2. Negative values
3. Zero values
4. Text values

You place a semi-colon (;) between parts.

They are specified in that order too. So if you want positive results with no decimal places and no "thousands" separators, negative numbers with the Alt-0151 character instead of a negative sign, two decimal places, and in RED, zeros to show as a blank, and text to show in a general manner, you would use the following:

0;[Red]—0.00;;@

Naturally, date formats are fine as well. Anything, in fact, that Excel allows for number formatting. So. If you want dates (that are not zeros, but rather are meaningful dates) to show as "12/23/2024", negative numbers to display as RED and an integer so these things that are seriously wrong in your results stand out, zeros to be blanks, and text to be "whatever" (let's color text RED as well, make that error stand out too), you could use:

mm/dd/yyyy;[Red]0;;[Red]@

You only expect dates and zeros, so the other two kinds of results will stand out like a RED thumb. Any real dates in Excel are ALWAYS positive values, so the first part makes them look normal. You can get zeros, but notice the two ;; with nothing between them: that means nothing at all will be displayed for a zero.

Supressing zeros can be done other ways, if desired, and not harmful. You can turn off the display of zeros at all, but that affects all cells, not just the ones you have this formula in. You can double the size and work of your formula by copying it all, then saying if THIS = 0, then "", else THIS... but that makes it a beast of double proportions and hard to understand later. Why do that when simply changing the formatting solves the problem, eh? A literal example of the logic using your formula would be:

=IF( MAX(IF('[YTD -2021.xlsx]OB'!$H$2:$H$23967=$C5,IF('[YTD -2021.xlsx]OB'!$J$2:$J$23967=$B$1,'[YTD -2021.xlsx]OB'!$A$2:$A$23967))) = 0, "", MAX(IF('[YTD -2021.xlsx]OB'!$H$2:$H$23967=$C5,IF('[YTD -2021.xlsx]OB'!$J$2:$J$23967=$B$1,'[YTD -2021.xlsx]OB'!$A$2:$A$23967))) )

You do that if you have to, but... you don't have to.

Also, if you would prefer a "0" (your other acceptable result) to be displayed rather than a blank cell, just put a single "0" character between those two ;;:

mm/dd/yyyy;[Red]0;0;[Red]@

By the way, there is something else you can do with this formatting functionality, but not germane to your question. However, you cannot mix this kind of formatting with that kind, so if you know about it and are using it in these cells, you will have to choose something like the doubled formula I showed above.

Answer 3

I would like to suggest few examples, ignore ZERO while calculation.

=MAX(IF($A$2:$A$15=F$1,IF($B$2:$B$15=F$2,IF($C$2:$C$15<>0,$C$2:$C$15,0))))

• Where this part IF($C$2:$C$15<>0 ignores ZERO, and will works with DATE value also, since basically DATE value is Numeric data, and then formatted to display as a DATE.

  =MINIFS($J$2:$J$20, $I$2:$I$20, ">=1/1/2021", $I$2:$I$20, "<2/28/2021", $J$2:$J$20, "<>0")
  

Another is:

=SUMPRODUCT(MAX(($A$2:$A$12=F1)*($B$2:$C$15=G2)*($D$2:$D$15<>0)*($D$2:$D15)))

N.B.

- Adjust cell references in the formula as needed.