max date formula combined with a condition?

Barbara Witt

Any idea how I could write a formula that gives me the max date for rows, where the the checkbox is checked?

I know to use the MAX formula to find the max date. But how can I restrict the search to only the rows, that have a checkbox checked?

Thanks for giving me some ideas.

Kelly Moore

@Barbara Witt

Try this

=MAX(COLLECT([your date]:[your date], [your checkbox]:[your checkbox], 1))

Remember this formula will have to reside in a date column since you are collecting a date.

cheers,

Kelly Moore

@Barbara Witt

Try this

=MAX(COLLECT([your date]:[your date], [your checkbox]:[your checkbox], 1))

Remember this formula will have to reside in a date column since you are collecting a date.

cheers,

Barbara Witt

Beautiful, KDM, that worked.

=MAX(COLLECT({DAX iOS Inventory Range 1}, {DAX Engagement

Tracking Range 8}, 1))

Now, I need to include the MAX formula with my existing IFERROR formula. I am using it to insure data is only displayed, if there is any. If there is no date to display, then it should leave the field blank. Can I combine the IFERROR fomula with the MAX formula to 1. only display a date if one was found and 2. display the greatest date that was found.

I have tried the following, but it will not pick up the most recent date.

=MAX(IFERROR(VLOOKUP([Project Number]@row, {DAX iOS Inventory Range 5}, 3), " "))

=(IFERROR(MAX(VLOOKUP([Project Number]@row, {DAX iOS Inventory Range 5}, 3), " "))

=(IFERROR(VLOOKUP([Project Number]@row, MAX( {DAX iOS Inventory Range 5}, 3), " "))

Thanks so much for your continued support.

Kelly Moore

Hey @Barbara Witt

Happy to help, I just need a bit of clarification.

When you say you want the IFERROR with your existing formula - do you mean the MAX(Collect) formula we just worked on, or an additional formula? If an additional formula, is that formula working without the IFERROR?

For the formula above, the IFERROR should be

IFERROR(MAX(COLLECT({DAX iOS Inventory Range 1}, {DAX Engagement

Tracking Range 8}, 1)),"")

If you are looking to execute the above IF only when another datefield is not empty then put an IF before the MAX(Collect)

IF(ISDATE([your date field]@row, MAX(COLLECT({DAX iOS Inventory Range 1}, {DAX Engagement

Tracking Range 8}, 1)))

Let me know and we can continue to build your formulas

Kelly

Barbara Witt

Sorry for the confusion, Kelly.

It appears your first assumption is right, but it will not give me the max date.

This is my original formula: IFERROR(VLOOKUP([Project Number]@row, {DAX iOS Inventory Range 5}, 3), " ")) I am matching up the project number and then it gives me the value in column 3, which is a date. If no date, then it keeps the field empty.

I could have several rows with the same project number and I only want the most recent date out of all the dates, that may be associated with that specific project number at row.

Can I use MAX in conjunction with my VLOOKUP (and the IFERROR, so it does not put a generic placeholder word if no date can be found)?

I tried your suggestions from above, but it will not display the max date.

IFERROR(MAX(VLOOKUP([Project Number]@row, {DAX iOS Inventory Range 5}, 3)), " ")

Well, there must be something else wrong. If I take the MAX back out, it will give me the correct date for 2 rows (there is only one project number and one date), however I have two other rows, that have only one date and one project number, and they are not showing up......

Oh no. Any suggestions? Thanks, Barbara

Christy Kooiman

I'm having the same issue except trying to find the minimum not max. I did your recommendation above and am getting # Invalid Column Value?

=MIN(COLLECT([End Date]:[End Date], [Team Member]:[Team Member], "Employee Name"))

What am I doing wrong? Any help would be greatly appreciated!!

Kelly Moore

Hey @Christy Kooiman

Is the column formatted as a date column? Also, are you looking for the word “Employee Name”, or are you looking for [Employee Name]@row (no quote marks)?

Christy Kooiman

Hi @KDM ...no it's a v-lookup from a different sheet!!! I didn't even think of that! However I tried in the other sheet and it's still not working. The date column is set to a date column

=MIN(COLLECT(Finish:Finish, PM:PM, "Employee Name"))

Kelly Moore

Let me know if you need any help or if you want to convert this to a cross sheet referenced formula (if you have the linkages)

Christy Kooiman

I'm now getting #Invalid Column Value in the new sheet. This sheet does have the date column as a true date column...

Employee Name is a lookup, not a column. The column is PM, and then I want to search on just one employee.

Kelly Moore

Hey

Two possible error causes to explore

1- In the date data you are wanting to collect - are there errors or non-date values in this range? How is that date originating?

2- whatever criteria that you are using for the formula you are testing - is there an 'response' it should actually pull from the data set, ie - if you changed the employee name or some other criteria, do you also receive an error?

If it is possible to give me a screenshot of the data and your formula, that will help me (and the community) troubleshoot.