Need Formula: "Is this person listed again in the next week?"

Genevieve P.

Hi @K L

https://community.smartsheet.com/discussion/comment/261898#Comment_261898

In response to your first comment, to only look forward, all we need to do here is take away the extra COUNTIF we added to look into Today's week. I've adjusted it to look and see if it's equal to or greater than ONE, because even if they're listed once next week, you want to know about it.

Try this:

=IF(COUNTIFS([Person Column Name]:[Person Column Name], [Person Column Name]@row, [Start Date]:[Start Date], IFERROR(WEEKNUMBER(@cell),0) = WEEKNUMBER([End Date]@row) + 1) >=1, "Yes", "No")

Your Start/End dates look correct to me, although I had built the original formula to only look into one Date column (ex. using the Start Date for all Date column instances). This formula is searching through the Start Date column to see if any of the Start Dates are one week ahead of the END DATE in that column now.

Genevieve P.

For your second comment,

https://community.smartsheet.com/discussion/comment/261902#Comment_261902

This would be the exact same formula, you would just change the + 1 in the WEEKNUMBER to be a -1 to look in the past week.

=IF(COUNTIFS([Person Column Name]:[Person Column Name], [Person Column Name]@row, [Start Date]:[Start Date], IFERROR(WEEKNUMBER(@cell),0) = WEEKNUMBER([End Date]@row) - 1) >=1, "Already Activated", "No Previous License")

Then you can add in the criteria about it saying "Activated" by just using another range and criteria:

=IF(COUNTIFS([Person Column Name]:[Person Column Name], [Person Column Name]@row, [Activated Column]:[Activated Column], "Activated", [Start Date]:[Start Date], IFERROR(WEEKNUMBER(@cell),0) = WEEKNUMBER([End Date]@row) - 1) >=1, "Already Activated", "No Previous License")

You would need to have this in a separate column than your current Activations column. This is because a column can either use a formula to auto-populate the cell content, or you can manually change what the column says; you can't do both. Does that make sense?

K L

@Genevieve P You are awesome, thank you! Okay so this is what I'm thinking:

The second function that you provided is working but the 1st one is giving a no, when it should be a yes. Every Fri, if the answer is no, I'll send an automation asking to deactivate it. So if there's two no's (see Person 2 below), it looks like it's giving the correct answer because Person 2 isn't listed in the following week, but then it will send 2 requests for the same person for the deactivation. I would prefer that the first date, 9/8, says yes and the second, 9/9, says no so it only sends one deactivation request.

I have this formula entered in the Next Week column:

=IF(COUNTIFS([Person Column Name]:[Person Column Name], [Person Column Name]@row, [Start Date]:[Start Date], IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER([End Date]@row) + 1) >= 1, "Yes", "No")

Start End Person Next week?

09/08/20   09/10/20   Person 1   Yes

09/08/20   09/08/20   Person 2   No <--Should be yes because the row below is starting on 9/9.

09/09/20   09/11/20   Person 2   No

09/08/20   09/10/20   Person 3   Yes

09/08/20   09/10/20   Person 4   Yes

09/11/20   09/11/20   Person 4   Yes <-- This is correct because they have a start date on 9/15. But I think the answer would be wrong like it is for Person 2 if Person 4 wasn't listed on 9/15.

09/08/20   09/10/20   Person 5   Yes

09/08/20   09/10/20   Person 6   No

09/08/20   09/10/20   Person 7   No

09/14/20   09/16/20   Person 8   No

09/14/20   09/14/20   Person 1   Yes

09/15/20   09/17/20   Person 1   Yes <--This is correct because they have a date that I didn't list at the bottom. I think it would be wrong similarly to Person 4, comment above.

09/14/20   09/16/20   Person 3   No

09/15/20   09/17/20   Person 9   No

09/15/20   09/17/20   Person 4   Yes

09/14/20   09/16/20   Person 5   Yes

09/14/20   09/14/20   Person 10   Yes

09/15/20   09/17/20   Person 10   Yes <--This is correct because they have a date that I didn't list at the bottom. I think it would be wrong similarly to Person 4, comment above.

09/15/20   09/17/20   Person 11   No

Also, when I use this formula in the Previously Activated column,

=IF(COUNTIFS([Person Column Name]:[Person Column Name], [Person Column Name]@row, [Activated Column]:[Activated Column], "Activated", [Start Date]:[Start Date], IFERROR(WEEKNUMBER(@cell),0) = WEEKNUMBER([End Date]@row) - 1) >=1, "Already Activated", "No Previous License")

and the cell reflects as "Already Activated", is there a way to duplicate "Already Activated" into the Activated column, every Thursday in the week before the start date? I have a request update automation that pushes a license activation request to a manager and once they activate the license, they respond in the webform "Activated". And the cell in the Activated column will record "Activated". So if I run the formula off the previous week's status and it's already activated, can I copy the words "Already Activated" into the Activate column so that it triggers an notification email to a coordinator?

Start End Person Next week? Previously activated? Activated

09/14/20   09/14/20   Person 1   Yes    Already Activated    (change to "Already Activated")

09/15/20   09/17/20   Person 1   Yes    Already Activated    (change to "Already Activated")

Paul Newcome

It seems like you are on a pretty good track here, but I would like to ask a quick question... Do you plan on having any overlap into different years? If so, using WEEKNUMBER([End Date]@row) - 1 and WEEKNUMBER([End Date]@row) + 1 may end up skewing your data.

Since the WEEKNUMBER function generates a numerical value as a stand-alone, having it generate a zero if you are currently in week one and looking back into the previous week (and similarly for being in the last week of the year looking forward) it won't throw an error.

To account for this you could subtract 7 from the date inside of the WEEKNUMBER function or you could use an IF statement (but the IF could get tricky for looking back one week into the previous year depending on if there are 52 or 53 weeks in that particular year).

WEEKNUMBER([End Date]@row) - 1

would change to

WEEKNUMBER([End Date]@row - 7)

and

WEEKNUMBER([End Date]@row) + 1

would change to

WEEKNUMBER([End Date]@row + 7)

Just thought I would throw that out there just in case you had any of that pesky overlap.

Genevieve P.

Thanks @Paul Newcome! We'll add that in.

@K L So I think I understand what you're saying now - you need it to look for a duplicate either next week OR in the current week but in the future. Is that correct?

If so, you can add together two COUNTs... one that checks next week based on the Week Number, and one that checks the next 7 days from that End Date. Keep in mind that this will create a double-count if the next 7 days also happen to be in the next Week Number, but since you're only looking for 1 instance this should be alright.

Try this:

=IF(COUNTIFS(Person:Person, Person@row, Start:Start, IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(End@row + 7)) + COUNTIFS(Person:Person, Person@row, Start:Start, >End@row, Start:Start, <(End@row + 7)) >= 1, "Yes", "No")

See that it's two COUNTIFS added together with the + before the IF criteria of being >=1.

This should give you a "Yes" for that second row, person 2, in the example you copied above.

Genevieve P.

In regards to your second question, I'm not sure I understand the process.

You can duplicate content into another column by using a formula, but then that column cannot be manually changed (or it will erase the formula which is creating the automation/duplication). Therefore, no, you cannot do this if the coordinator is making changes and adding "Activated" manually in that same column.

Instead, you could now use your helper column that shows if it's already activated to either provide that information to the coordinator or to use as a criteria in your alert (to either send the notification or not send the update request). Does that make sense?

K L

Thanks @Paul Newcome!

Hi @Genevieve P , Thanks for getting back to me. This worked perfectly!

=IF(COUNTIFS([Person]:[Person], [Person]@row, [Start Date]:[Start Date], IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER([End Date]@row + 7)) + COUNTIFS([Person]:[Person], [Person]@row, [Start Date]:[Start Date], [End Date]@row, [Start Date]:[Start Date], <([End Date]@row + 7)) >= 1, "Yes", "No")

For the second part about copying the cell response, I see what you mean about running the automation off the helping column and I like your idea! I'll work to get that incorporated into the automation. The only last issue I can see is when there's a person on two dates, who didn't have a license in the previous week, and their license needs to be activated. Each person only has one license and so there aren't multiple licenses for the same person. Ultimately, I'd like one row to say needs to be activated and the other to say already activated, so that only row for that person 1 is sent for the activation request.

09/14/20   09/14/20   Person 10   Needs to be Activated

09/15/20   09/17/20   Person 10   Needs to be Activated <--I'd like this one to say "Already Activated" so that the activation request isn't sent as a duplicate.

This is the function I have entered currently:

=IF(COUNTIFS([Person]:[Person], [Person]@row, [Activated Column]:[Activated Column], "Activated", [Start Date]:[Start Date], IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER([End Date]@row) - 1) >= 1, "Already Activated", "Needs to be Activated")

Paul Newcome

Happy to help. 👍️

You're in good hands with @Genevieve P, but I figured I would go ahead and throw in that warning since I have learned the hard way (and more times than I care to admit haha) about making sure to account for multiple years/months/days/whatever increment you are working in.

Genevieve P.

@K L

For your second formula, you can add in the same logic that we did to finish of your first one. Instead of only looking in the last week, you can also look in the last 7 days and in the current week.

Try this, does it match all of your criteria?

=IF(OR(COUNTIFS(Person:Person, Person@row, [Activated Column]:[Activated Column], "Activated", [End Date]:[End Date], IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER([Start Date]@row - 7)) >= 1, COUNTIFS(Person:Person, Person@row, [End Date]:[End Date], <[Start Date]@row, [End Date]:[End Date], >=([Start Date]@row - 7), [End Date]:[End Date], IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER([Start Date]@row)) >= 1), "Already Activated", "Needs to be Activated")

Broken down:

=IF(OR(

If this person has an End Date last week (compared to their current Start Date) and was set to Activated,

COUNTIFS(Person:Person, Person@row, [Activated Column]:[Activated Column], "Activated", [End Date]:[End Date], IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER([Start Date]@row - 7)) >= 1,

OR

If the same person has a Start Date that is within 7 days of when they last had an End Date, and that End happens to be within this current week,

COUNTIFS(Person:Person, Person@row, [End Date]:[End Date], <[Start Date]@row, [End Date]:[End Date], >=([Start Date]@row - 7), [End Date]:[End Date], IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER([Start Date]@row)) >= 1),

then say "Already Activated". Otherwise, say "needs to be activated".

"Already Activated", "Needs to be Activated")

Let me know if we've finally got it! 🙂

K L

Hi @Genevieve P I thought that it worked but then I see this. Would you please help look into it? We are sooo close!

I think what I'm seeing is if I take the formula and put it into all cells in the column, it messes up earliest date but fixes any secondary dates. I would like to a single formula entered into all cells of the column and not have to enter a separate function into cells that have more than one person in the same week.

I used the formula from your last message:

=IF(OR(COUNTIFS(Person:Person, Person@row, [Activated Column]:[Activated Column], "Activated", [End Date]:[End Date], IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER([Start Date]@row - 7)) >= 1, COUNTIFS(Person:Person, Person@row, [End Date]:[End Date], <[Start Date]@row, [End Date]:[End Date], >=([Start Date]@row - 7), [End Date]:[End Date], IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER([Start Date]@row)) >= 1), "Already Activated", "Needs to be Activated")

09/14/20   09/14/20   Person 10    Needs to be Activated   Activated <--This is correct due to info that I didn't include here. But it's possible it would be incorrect like below since it's the earlier date of the two that Person 10 has in the same week.

09/15/20   09/17/20   Person 10   Already Activated   Activated

09/21/20   09/21/20   Person 10   Needs to be Activated <--This should be "Already Activated" since Person 10 has one activated on 9/14 and 9/15-17 dates.

09/22/20   09/24/20   Person 10   Already Activated

09/25/20   09/25/20   Person 10   Already Activated

Thanks!

Genevieve P.

Hi @K L

I'm not getting the same result as you, although I have the same dates and same formula... see row 3 below:

Keep in mind that you are looking at the "Activated Column" in this formula, so you'd need that to state "Activated" for the previous week before it will return "Already Activated" in this week.

K L

Hi @Genevieve P ! I'm not sure what happened but it is working now!!! Thank you!

As I try to get this implemented, the last issue is with the "already activated" ones. Those won't be sent for license activation because they are already activated and the license status column is left blank. Can we include a way for it to say: Change to "Already Activated" if the person was listed in the previous week and their license is marked as "Active" OR if it shows "Already Activated" and Blank?

The ones this week all say "Activated", since I sent all for license activation, regardless if they needed it or not and my contact selected "Activated" for each one. Then, starting next week in green, I can see how there will be blank spots which impacting the week after that in orange.

I have the ones highlighted in red below:

Genevieve P.

Hi @K L

This formula is built specifically to look in the last week and check to see if the word "Activated" is in that Activated column. If there's no other status it needs to look for in that column and you just want to see if they appear in the previous week (regardless of what's in the Activated column) then you can take out that criteria:

=IF(OR(COUNTIFS(Person:Person, Person@row, [End Date]:[End Date], IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER([Start Date]@row - 7)) >= 1, COUNTIFS(Person:Person, Person@row, [End Date]:[End Date], <[Start Date]@row, [End Date]:[End Date], >=([Start Date]@row - 7), [End Date]:[End Date], IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER([Start Date]@row)) >= 1), "Already Activated", "Needs to be Activated")

Or, if there is going to be another value in that Activated column (such as "rejected"), then you can count if it's not that value by using <> to indicate "not". This will then incorporate blank values, but ignore it if the Activated column has a rejection:

=IF(OR(COUNTIFS(Person:Person, Person@row, [Activated Column]:[Activated Column], <> "Rejected", [End Date]:[End Date], IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER([Start Date]@row - 7)) >= 1, COUNTIFS(Person:Person, Person@row, [End Date]:[End Date], <[Start Date]@row, [End Date]:[End Date], >=([Start Date]@row - 7), [End Date]:[End Date], IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER([Start Date]@row)) >= 1), "Already Activated", "Needs to be Activated")

However you cannot reference your own column (this "License in previous week? Activate?") to look for the "Already Activated" value because you'd get a Circular Reference error, since it would try to read its own cell along with the rest of the column. Does that make sense?

K L

@Genevieve P Thanks for staying on this with me, I appreciate you!

After looking at how one helping column looks forward if a person has an upcoming date and how the other helping column looks backwards, I think this formula will work! The only tweak I would do is if it says "Deactivated" or "Needs to be Deactivated" in the Activated column, then change to "Needs to be Deactivated". That would only be for cases when the cell was manually changed to "Deactivated" or "Needs to be Deactivated". If there's a way to add that in, that would be great to know.

IF(OR(COUNTIFS(Person:Person, Person@row, [End Date]:[End Date], IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER([Start Date]@row - 7)) >= 1, COUNTIFS(Person:Person, Person@row, [End Date]:[End Date], <[Start Date]@row, [End Date]:[End Date], >=([Start Date]@row - 7), [End Date]:[End Date], IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER([Start Date]@row)) >= 1), "Already Activated", "Needs to be Activated")

My last question on this is, can my functions look forward and backwards into other master sheets? We basically split schedules into quarters. So can the end of Q3 look into Q4, and likewise, can the beginning of Q4 look backwards into Q3?

Genevieve P.

Hi @K L

That gets a bit tricky, adding in those extra criteria. I have to admit that I am having a hard time wrapping my head around it, because it sounds like you could have two possible scenarios within the same week - an activated row and a deactivated row. Your formula would count both of those, and would just return the first result, not necessarily an accurate result.

I would suggest instead perhaps thinking more about the workflow of how this sheet is being used. For example, based on what you already have set up (an Activated column and a "is this person in the next week" column), you can use Conditional Formatting to highlight rows where "Deactivated" is in the same row as a "is this person in the next week" column:

This could be a visual cue to ensure that the same person in the following week is then activated.

Or, you can create a Report to filter down exactly what you're looking for - you can filter by person and by date to create a window into the sheet for this week and next, to ensure all your activations are up to date. You could then Sort the Report by Person and Dates to have them in order for the two weeks.

Then once a week you could open up just the Report to make sure everything looks good. It will even bring in conditional formatting from your rows into the report, to help with any visual cues you set up. Does this make sense?