Forum Discussion
MAXIFS with DATEVALUE
Maybe, I'm not sure which ranges are to be taken. That was only to illustrate that named range causes no error with MAXIFS if only it returns correct data. In our case that shall be dates, not texts as with your variant.
To be sure you may return spill such named range at any place and check with ISTEXT() do you have texts or dates.
First parameter shall be the range, not formula. Convert first column into dates first.
Thanks, Sergei. VisitDateText, GearE, and SurvE are named ranges in the VisitData worksheet. Many cells in those named ranges are blank in this workbook. The VisitDate named range in the LF worksheet converts the text to a DATEVALUE. It seems the #VALUE results in VisitDate are causing the MAXIFS function in Q6 to return zero.
Formula in Q6 is
=MAXIFS(VisitDate,GearE,"BOOM SHOCKER",SurvE,$B$3)we try to find max on VisitDate. The latest returns array of texts with some zeroes at the end which correspond to blank cells in the source. MAXIFS ignores texts and returns max from numbers. 0 in our case or 1900-01-00 in date format.
Not to be dependant on locale I converted texts to dates as
=DATE(RIGHT(VisitDateText,4), LEFT(VisitDateText,2), MID(VisitDateText,4,2) )which correctly returns dates. If MAXIFS on that spill it returns some date.
Please check in attached.