Max Collect function not working with date

remkin

Hey,

Ive got a drivers record sheet. on the sheet the date is entered and there are columns for the driver name and the asset number. Im trying to return the date for the last time a prestart report was done on a particular asset number.

=MAX(COLLECT({Daily Driver Records Range 2}, {Daily Driver Records Range 1}, [Column5]1))

Where

Daily Driver Records Range 2 = the date of the entry

Daily Driver Records Range 1 = the asset number

[Column5]1 = the asset number im looking for

The dates in my corresponding sheet are set to date type but its showing #INVALID COLUMN VALUE. If I change the return value (range) to asset number and the criteria range and criteria to the driver it works. the Collect function doesnt like the date. Its definately set as a date column and there no erroneous date in it.

Dont know what im doing wrong here

Genevieve P.

Hi @remkin,

I've tested this on my own sheet and it works as expected. Keep in mind that not only do the columns referenced need to be Date Type, but the column that contains the formula also needs to be a Date Type column. Can you confirm that this is true?

If this isn't the issue, could you provide screen captures of the sheet with the formula and also the sheet with the referenced columns? (Blocking out any sensitive data).

Thanks!

Genevieve

J Tech

Hi @Genevieve P @J. Craig Williams @Andrea Mayer

Does this work on the sheet summary? I have tried but cant seem to get it to work.

I have the below formula in sheet summary:

=IF(MAX(COLLECT([Date Value]:[Date Value], Venue:Venue, "Sample Venue", Type:Type, "Major")) = "0", "", (MAX(COLLECT([Date Value]:[Date Value], Venue:Venue, "Sample Venue", Type:Type, "Major"))))

Genevieve P.

Hi @J Tech

Yes, it should work in a Sheet Summary field. Are you getting the #UNPARSEABLE error? It looks like you might just have an extra parentheses before the second MAX and an extra closing one at the end. Try this:

=IF(MAX(COLLECT([Date Value]:[Date Value], Venue:Venue, "Sample Venue", Type:Type, "Major")) = "0", "", MAX(COLLECT([Date Value]:[Date Value], Venue:Venue, "Sample Venue", Type:Type, "Major")))

J Tech

Hi @Genevieve P,

I seem to get #INVALID COLUMN VALUE error.

Genevieve P.

Hi @J Tech

Is your [Date Value] column a Date column? And you want to return the MAX date?

If so, your Summary Field where the formula is entered will need to be a Date type of field, as well. (You can Edit Field Properties to change this.)

If updating your field type doesn't fix the issue, a screen capture of your sheet (removing any sensitive data) would be helpful!

Thanks,

Genevieve

J Tech

Hi @Genevieve P ,

My fault is wasn't set to date in the summary field, so all working now.

Genevieve P.

Glad to hear it's working! 😊