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?
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.HEREis one of the more recent ones.
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?
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.
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[email protected]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.
Ok, now I think I figured out why I thought my formula wasn't working. The system columnCreateddate doesn't match what Smartsheet is evaluating the value as:
TheCreatedcolumn above is originating from another sheet that captures Form data which has an automation to move rows into this sheet. The original source sheetCreatedwas setup as a system column created (date ) column.
TheRespondedcolumn 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 helperRespondedcolumn and directly referenceCreatedin the =MAX(COLLECT function. I even tried using a =DATEVALUE([Created]@row) function in theRespondedcolumn - 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.
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.HEREis one of the more recent ones.
@Paul NewcomeThanks! 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 ✭✭✭✭✭✭
David Joyeuse ✭✭✭✭✭
![\"image.png\"](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/0T1JMLQOPB6K\/image.png\")
<\/img><\/a>\n <\/div>\n<\/div>\n
