Issue with MAX COLLECT. I am trying to collect the "most recent" date of each unique item.

joyce victoria

I am struggling to understand where I am going wrong. The most recent entry is 01/20/25, but smartsheet keeps pulling it as 01/21/25, when there is no 1/21/25 data entry for this unique item.

=MAX(COLLECT([Created Date_ED]:[Created Date_ED], [Phone Item #]:[Phone Item #], [Phone Item #]@row))

When I try to use a helper column (Creation Date) and use that in the formula instead, I get a blank.

KPH

Hi @joyce victoria

I don't think it is you. I think you might be based somewhere that is not in the UTC time zone. There is a problem with the way Smartsheet stores the created dates. If 01/20/25 8:12 PM is beyond midnight in UTC, then that will be returned as 01/21/25. You can test my theory by adding a temporary column and the formula

=DATEONLY([Created Date_ED]@row)

Have a look at what is returned. I expect the dates will not be the same as the dates you are currently seeing for any timestamps beyond midnight UTC.

If that is the case, there is a workaround. We can create a helper column like you have and use that in the MAX formula instead. The helper column does need to be a date format column (not number/text) for the MAX to work. Is that what you have at the moment?

joyce victoria

@KPH You are correct, I’m PST. I was getting super frustrated wondering why it kept pulling it that way.

I did use the MAX formula towards the helper column in Creation Date, but then I have the issue of sometimes a Phone Item # has the same date. For instance, Phone 1 could have been at 3/24/2025 9:00 AM, and then 3/24/2025 3:00PM. Is there a way to make it so that it takes into account the time as well?

KPH

Could you create another helper column for the time (in 24 hour clock with decimal minutes) and then do a max on the time if the date is the max date?

Paul Newcome

You can use the MAX/COLLECT pulling from the original "created" date/time column and then convert it into a string by "adding" empty quotes at the end of it. Dropping that into a text/number column will get you the date/time displayed for the most recent.

=MAX(COLLECT(……………..)) + ""