MAX(COLLECT Not Returning Anything

sam.hughes

Hi! I'm running into an issue using MAX and COLLECT that I was hoping someone in community could help out with.

I've got a sheet that collects billing information. Among other things, a sheet for each employee contains a Project ID and a Monthly Report End Date.

Each month, new rows are added to the sheet. As projects tend to take longer than one month, we can generally expect multiple rows will have the same Project ID. We'd like to include a column that indicates, for each project, the last Monthly Report End Date.

The Project ID column is a Text/Number column. The Monthly Report End Date column is a Date column. The Latest Bill Date column is a Date column, but it is not restricted to dates.

The formula we are using is copied below:

=MAX(COLLECT([Monthly Report End Date]:[Monthly Report End Date], [Project ID]:[Project ID], [Project ID]@row))

For some reason, that doesn't return anything. If I turn both the Monthly Report End Date column and the Latest Bill Date column to Text/Number columns, it returns "0". Any assistance you could provide would be greatly appreciated!

jmyzk_cloudsmart_jp

Hi @sam.hughes

Strange. Your formula is working fine.

https://app.smartsheet.com/b/publish?EQBCT=d832297ca4374a53b6956ebab6a297e8 (Link to the demo sheet)

slopez9

Hello,

I have a similar issue. Both columns are date columns but a date doesn't seem to be populating with the expected date. Any answers on why the formula worked for @jmyzk_cloudsmart_jp and not @sam.hughes?