Return the largest date value from an external sheet array

Gordon

Hello,

I'm trying to return the max date from an external referenced sheet's system Created date column ("Response Date") based on matching the multiple values in another column in the external sheet an email address/name from the current sheet.

I've tried using =MAX(COLLECT(Response Date, Email Address Column, Email Address in current sheet)), but it seems to be returning the max date regardless of the email address criteria.

Do I need to do a MATCH first based on email address, then COLLECT? Or is the proper logic to use INDEX, MATCH and COLLECT?

Any help or insights are greatly appreciated!

Paul Newcome

It is a time zone thing. There are quite a number of posts here in the Community that have solutions on how to pull the correct date. HERE is one of the more recent ones.

David Joyeuse

Hi @Gordon

=MAX(COLLECT({Response Date}, {Email Address},[Email Address]@row) is what you are looking for.

Adapt ranges and email address column name to what you are effectively using in your sheets.

Hope it helped!

Gordon

Hi @David Joyeuse

Thank you for the suggestion. Unfortunately, when I use your formula (replacing {Response Date} and {Email Address} with the external reference to the column range in the other sheet) the formula returns the latest date in the {Response Date} column, ignoring the email address criteria.

Does COLLECT work with external sheet references to an entire column? Do the columns have to be next to each other in the external sheet?

Thanks for your help!

Paul Newcome

COLLECT should work for this, and the columns do not have to be next to each other.

Make sure the email data is a match on both sheets.

Gordon

Does the column type have to match between the Criteria Range and Criteria?

The {Email Address} is a system Created By column that is from a Smartsheet form and the {Email Address}@row reference in the current sheet is a contact list column. The {Response Date} column is setup in the external sheet as a system Created on date column.

Thanks.

Paul Newcome

You will need to make sure the data in the Created By column matches the data in the [Email Address] column. If you have an email in one sheet and a contact in the other, they will not match.

So if you have john.doe@email.com in the Created By column and John Doe in the contact type column, they will not match on each other because the text strings are different.

Gordon

Both columns appear in Smartsheet as email addresses, but perhaps the column type is causing the mismatch and criteria to not be applied?

Gordon

Ok, now I think I figured out why I thought my formula wasn't working. The system column Created date doesn't match what Smartsheet is evaluating the value as:

The Created column above is originating from another sheet that captures Form data which has an automation to move rows into this sheet. The original source sheet Created was setup as a system column created (date ) column.

The Responded column above is setup as a date column type and is a simple reference =[Created}@row formula.

As shown above, for whatever reason, Smartsheet is showing the date incorrectly in the results in the formula.

The 8/21/20 result is what was being output from my =MAX(COLLECT formula above, which is why I was thinking my formula was wrong.

I get the same "8/21/20" result even if I don't use the helper Responded column and directly reference Created in the =MAX(COLLECT function. I even tried using a =DATEVALUE([Created]@row) function in the Responded column - same result.

Any idea on what is going on here? I'm totally confused why 8/20/20 at 4:28PM is the same as 8/21/20. You can see the same error in other rows as well, while some dates are correct. 😕

Paul Newcome

It is a time zone thing. There are quite a number of posts here in the Community that have solutions on how to pull the correct date. HERE is one of the more recent ones.

Gordon

@Paul Newcome Thanks! I didn't realize that Smartsheet was being so "smart" about time zones and dates. You'd think there would be consistency at the user level such that formulas and functions should all be "smart" enough to adjust for this. I guess I sort of understand how & why they had to build out date/time based on UTC and convert at the user level. Still though, it leads to a lot confusion.

I solved my issue by stripping out the date text from the Created column and converting it back to a date value.

Paul Newcome

Glad you got it sorted and happy to help. 👍️