How to use MAX COLLECT for dates

alihwork

Hello,

I need to pull the most recent date for maintenance based on the most recent date entered in another column. I have several pieces of equipment I am working with, so I need to specify with the Equipment ID.

I have tried =max(collect([Last PM Date]:[Last PM Date], [Equipment ID]:[Equipment ID], US-149)) but I receive an #UNPARSEABLE error, see row 33 on the image.

I have also tried =max(collect([Last PM Date]:[Last PM Date], [Equipment ID]:[Equipment ID], [US-149])) but I also receive an #UNPARSEABLE error, see row 34 on the image.

I have also tried =max(collect([Last PM Date]:[Last PM Date], [Equipment ID]:[Equipment ID], [US-149]@row)) but I also receive an #UNPARSEABLE error, see row 35 on the image.

Column type for Equipment ID is "text/number," column type for Last PM Date & Most Recent PM are both "date."

Thanks!!😊

Kelly Moore

Hey @alihwork

The unparseable is caused by missing quotes around "US-149" if you are calling out that ID specifically. Being alphanumeric, this is a text string and text strings require quotations.

=max(collect([Last PM Date]:[Last PM Date], [Equipment ID]:[Equipment ID], "US-149")) 

I would suggest adding a requirement for the [Last PM Date] to be a date - this will help prevent errors in the formula. I will also modify the formula so that it is dynamic for the row

=MAX(COLLECT([Last PM Date]:[Last PM Date],[Last PM Date]:[Last PM Date], ISDATE(@cell), [Equipment ID]:[Equipment ID], [Equipment ID]@row)) 

Will either of these work for you?

Kelly

alihwork

I also filled up the rest of the column "Last PM Date" with dates, but it didn't help.

Kelly Moore

Hey @alihwork

The unparseable is caused by missing quotes around "US-149" if you are calling out that ID specifically. Being alphanumeric, this is a text string and text strings require quotations.

=max(collect([Last PM Date]:[Last PM Date], [Equipment ID]:[Equipment ID], "US-149")) 

I would suggest adding a requirement for the [Last PM Date] to be a date - this will help prevent errors in the formula. I will also modify the formula so that it is dynamic for the row

=MAX(COLLECT([Last PM Date]:[Last PM Date],[Last PM Date]:[Last PM Date], ISDATE(@cell), [Equipment ID]:[Equipment ID], [Equipment ID]@row)) 

Will either of these work for you?

Kelly

alihwork

Hi @Kelly Moore ,

You're a lifesaver! For now, I will go with your first suggestion.

Thank you for your help :)

V.Rodrig

Hi @Kelly Moore ,

I have a similar situation that I'm having a difficult time with.

I need to pull the latest date from the "Active - Time Stamp ON" column based on the DNIS being the same. Seeing below the screenshot, the first 5 rows have the same DNIS of 0012. So the date I need it to pull back is the latest date of 06/13/2022.

I have tried various versions of the MAX(COLLECT function but continue to get a value of blank. The "Active - Time Stamp ON" and "Max Date" columns are both date columns. I have changed the "Max Date" column to text/number to see if that helps but I get a 0.

Here's what I have used:

=MAX(COLLECT([Active - Time Stamp ON]:[Active - Time Stamp ON], DNIS:DNIS, DNIS@row))

=MAX(COLLECT([Active - Time Stamp ON]:[Active - Time Stamp ON], DNIS:DNIS, "0012"))

Kelly Moore

Hey @V.Rodrig

I'm about to run to a meeting and will look at this tonight but in the meantime, try this. I noticed you have some missing dates and sometimes the MAX function doesn't know how to evaluate that. Sometimes this works.

=MAX(COLLECT([Active - Time Stamp ON]:[Active - Time Stamp ON], [Active - Time Stamp ON]:[Active - Time Stamp ON], ISDATE(@cell), DNIS:DNIS, DNIS@row))

Did that do anything for the problem?

Kelly

V.Rodrig

@Kelly Moore - unfortunately that didn't work.

Kelly Moore

Hey @V.Rodrig

The issue the formula was having was with the leading apostrophe that is embedded in the DNIS cells. I added an apostrophe enclosed in the double quotes.

=MAX(COLLECT([Active - Time Stamp ON]:[Active - Time Stamp ON], [Active - Time Stamp ON]:[Active - Time Stamp ON], ISDATE(@cell), DNIS:DNIS, "'" + DNIS@row))

Will this work for you?

Kelly

V.Rodrig

@Kelly Moore - yes that worked!! Thank you so much 😁