Return the largest date value from an external sheet array
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!
Best Answer
-
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.HEREis one of the more recent ones.
thinkspi.com
Answers
-
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 ✭✭✭✭✭
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.
thinkspi.com
-
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[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.
thinkspi.com
-
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 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.
-
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.HEREis one of the more recent ones.
thinkspi.com
-
Gordon ✭✭✭✭✭
@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 ✭✭✭✭✭✭
Help Article Resources
Categories
If I understand correctly, this may help.<\/p>
=AVERAGEIF({date of request}, MONTH(@cell) = 2, {Ack SLA})<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":107116,"type":"question","name":"I'd like to create a column formula that references a specific cell with acceptable syntax","excerpt":"I have a sheet that has a column of dates that autofill based on another date. Specifically, each row has target end dates based on weeks ahead of the date that I want to reference. This is to make sure tasks are started on time in relation to the overall start date. My formula is currently: =[Target End Date]1 - ([Task…","snippet":"I have a sheet that has a column of dates that autofill based on another date. Specifically, each row has target end dates based on weeks ahead of the date that I want to…","categoryID":322,"dateInserted":"2023-06-29T23:48:12+00:00","dateUpdated":null,"dateLastComment":"2023-06-30T22:48:54+00:00","insertUserID":163028,"insertUser":{"userID":163028,"name":"jcabaniss","url":"https:\/\/community.smartsheet.com\/profile\/jcabaniss","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-01T02:58:52+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":129958,"lastUser":{"userID":129958,"name":"Lucas Rayala","title":"Program Manager","url":"https:\/\/community.smartsheet.com\/profile\/Lucas%20Rayala","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!3C9S_9DsC3w!XMDTX-y-BOY!7HVumwhOGBb","dateLastActive":"2023-07-01T15:43:12+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":35,"score":null,"hot":3376249626,"url":"https:\/\/community.smartsheet.com\/discussion\/107116\/id-like-to-create-a-column-formula-that-references-a-specific-cell-with-acceptable-syntax","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107116\/id-like-to-create-a-column-formula-that-references-a-specific-cell-with-acceptable-syntax","format":"Rich","lastPost":{"discussionID":107116,"commentID":383416,"name":"Re: I'd like to create a column formula that references a specific cell with acceptable syntax","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/383416#Comment_383416","dateInserted":"2023-06-30T22:48:54+00:00","insertUserID":129958,"insertUser":{"userID":129958,"name":"Lucas Rayala","title":"Program Manager","url":"https:\/\/community.smartsheet.com\/profile\/Lucas%20Rayala","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!3C9S_9DsC3w!XMDTX-y-BOY!7HVumwhOGBb","dateLastActive":"2023-07-01T15:43:12+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-30T15:52:25+00:00","dateAnswered":"2023-06-30T00:15:36+00:00","acceptedAnswers":[{"commentID":383245,"body":"
@jcabaniss<\/a> , swap out this:<\/p>
