Get maximum date within a range, without considering blank dates

Question

How can one get the maximum date within a range, for non-blank dates? Presently the maxifs formula is giving out an 9999 date on blank date rows and considering that as the highest date and filling the same on the rest of the rows.. please see desired output.. thanks in advance..

Answer 1

Hello there,

Sorry to hear you're experiencing issues with Excel and hope I can help with the following solution:

1)You can use the MAX function combined with IF and ISBLANK functions to achieve this. Here's how you can do it: 1.1) Assuming your dates are in column A, you can use the following formula: 1.2) =MAX(IF(ISBLANK(A:A), 0, A:A))

1.3) This is an array formula, so you need to enter it with Ctrl + Shift + Enter. 1.3.2)This formula checks each cell in column A. If a cell is blank, it substitutes it with 0, otherwise, it keeps the original date. Then, it finds the maximum date among these non-blank dates. 1.3.3)This should give you the maximum date within the range, excluding blank dates.

I hope the following solution helps you and should you have anymore problems/concerns please do not hesitate to contact me again.

Thanks,

Parth P.

Answer 2

Hi,

In cell I3, enter this formula and drag down

=if(F3=12,maxifs($D$3:$D$8,$A$3:$A$8,F3),"")

Hope this helps.

Answer 3

Use a Pivottable in a Tabular form:

Create a PivotTable to analyze worksheet data - Office Support

ID, Type, Status in Rows

Max of Date in Values

If you need further help, we need to see your (sample) file.
Why a sample file is important for troubleshooting. How to do it. - Microsoft Community

Andreas.