SOLVED

# MAXIFS with DATEVALUE

Copper Contributor

# MAXIFS with DATEVALUE

I'm trying to enter the MAXIFS function in E2, but Excel doesn't recognize the formula.  Thanks for any help you can offer.

10 Replies

# Re: MAXIFS with DATEVALUE

Yes, my version of Excel does support the MAXIFS function. I've used it before.

# Re: MAXIFS with DATEVALUE

First parameter shall be the range, not formula. Convert first column into dates first.

# Re: MAXIFS with DATEVALUE

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.

# Re: MAXIFS with DATEVALUE

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.

# Re: MAXIFS with DATEVALUE

T6 and T8 show that the =DATEVALUE conversion in P6 produced the same result as the =DATE conversion in R6.  Using a named range in the MAXIFS formula seems to cause the problem.  See T10:T13 in attachment.  VisitDate has the same size and shape as GearE and SurvE.

# Re: MAXIFS with DATEVALUE

I converted FishData!P:P with dates as texts into real data in FishData!BH:BH. In LF sheet reference is changed accordingly

VisitDate remains the same

Now use it in formula, it works

The only difference VisitDate now returns correctly converted dates, not texts.

# Re: MAXIFS with DATEVALUE

The MAXIFS result 05/07/1962 is incorrect because criteria2 (SurvE,\$B\$3) has only dates in 2022.

The text to date conversion should be on VisitDateText in VisitData!M:M, not FishData!P:P.  I have a mistake in the reference for VisitDate.

best response confirmed by scheij (Copper Contributor)
Solution

# Re: 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.

# Re: MAXIFS with DATEVALUE

My entire problem is traced back to my mistake in the reference to the range named VisitDate.  With that reference correct now, MAXIFS gives correct result for both text to date conversions in LF!P:P and LF!R:R. I wanted to attach a final copy, but maybe there is a limit.  Thank you for your help with this

# Re: MAXIFS with DATEVALUE

@scheij , thank you for the update, glad to help

1 best response

Accepted Solutions
best response confirmed by scheij (Copper Contributor)
Solution

# Re: 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.