Identifying latest entry that covers an existing date range for a team member
I've spent too much time going around in circles on this - so time to engage the hive mind :)
Simple to explain (I hope), but hard for me to find a way to get the effect I need :(
"Identity the latest entry for [Initials] if previous entries for [Initials] cover the same date range"
So [Row_ID] 15 is the latest entry for [Initials]=RS and [Row_ID]'s 17 and 18 are the latest entries for [Initials]=AA
Need a column formula that will allow me to filter/move rows out of the sheet so that I'm left with (in the example above) just [ROW_ID]'s 15,17 and 18
Answers
-
Hey @Snaillybob
Thanks for providing the screenshot -its always enormously helpful to have one. In this approach below you'll need a helper column if you don't already have one, to hold the formula. A checkbox column will do. This will allow you to easily filter a sheet or create a report for this information.
The one watchout with this formula is the known issue of the Created time vs your timezone. This is only an issue if your timezone is different than UTC AND (and this is a big and) you are collecting data in the time when UTC crosses midnight and your timezone crosses midnight. The issue is caused by the fact that, although the Date Created appears to be in your timezone, the date is actually stored as UTC(0). As stated above, the discrepancies occur when rows are being created when UTC has already crossed midnight and your local timezone has not- or vice versa.This is only an issue if your timezone is different than UTC AND you are collecting data during this window of time between the two midnights.
=IF([Row_ID]@row = MATCH(MAX(COLLECT([Row_ID]:[Row_ID], Initials:Initials, @cell = Initials@row, Created:Created, MAX(@cell) = Finish@row)), [Row_ID]:[Row_ID], 0), 1)
Will this work for you?
Kelly
-
Thanks Kelly - will plug this in today and let you know, and thank you for the explanation of time zones, which wouldn't have occurred to me.
-
Hi Kelly - I'm getting a #NO MATCH for some of the rows :(
-
Hey @Snaillybob
I may have misunderstood your process. I compare the Created date to the finish date to find the most recent of the rows. The No Match dates are in the future so there is no match. I can make the words No Match disappear and leave a blank instead. Is this real data or just test data in your example above?
-
Thanks for the quick reply and for trying to tackle this for me:) - the data is test data, but the structure is from a bigger resource planning sheet.
Maybe a story will help describe what I'm trying to do?
All the team fill in a smartsheet form to update a sheet with where they are going to be today (and in the future if they know).
Roger submits his form saying he will be on site 20 through 24 Feb. All good - the sheet shows Roger as being on site :)
On 21 Feb Roger submits a form that says he will be off site from 22 through 28 Feb.
All is good until we tick through to 22 Feb - a report showing where everyone is TODAY() now has 2 entries for Roger
What I am trying to achieve is the identification of Roger's 'on site' form submission as being superceded (in terms of start/finish dates covered) by his 'off site' form submission. This will allow me to flag it and automate moving it off the sheet (so it doesn't show up in the shared google calendar the sheet is published to).
Roger's first entry (20-24 Feb) is superceded by his second entry (22-28 Feb)
Roger has also submitted PTO for March, September and December, so his latest February change isn't the latest submission from him.
Does that help, or just muddy the water?
-
-
Hey @Snaillybob
Will this work for you?
=IF(Created@row = MAX(DISTINCT(COLLECT(Created:Created, Finish:Finish, ISDATE(@cell), Initials:Initials, Initials@row, Finish:Finish, Finish@row))), 1)
Kelly
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.5K Get Help
- 367 Global Discussions
- 202 Industry Talk
- 432 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 447 Show & Tell
- 29 Member Spotlight
- 1 SmartStories
- 285 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!