Identifying latest entry that covers an existing date range for a team member

Snaillybob

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

Kelly Moore

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

Snaillybob

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.

Snaillybob

Hi Kelly - I'm getting a #NO MATCH for some of the rows :(

Kelly Moore

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?

Snaillybob

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?

Snaillybob

Kelly Moore

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