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

K L

I'm trying to figure out how to build a formula with this logic. When a person is listed on a row with start and end dates, are they listed again for an upcoming event in the next week? Is it possible to create a formula that gives a yes/no answer. Or gives a count of how many times a person is listed in the next week? Or can half of the logic be built in a formula and the other half in automation filters?

08/18/20  08/20/20  Person 10

08/18/20  08/20/20  Person 11

08/18/20  08/20/20  Person 12

08/25/20  08/27/20  Person 13

08/25/20  08/27/20  Person 8

08/26/20  08/28/20  Person 9

08/25/20  08/27/20  Person 2

08/24/20  08/24/20  Person 2

08/26/20  08/28/20  Person 11

09/01/20  09/03/20  Person 5

09/04/20  09/04/20  Person 5

09/01/20  09/03/20  Person 9

08/31/20  09/02/20  Person 3

09/01/20  09/03/20  Person 10

Thanks for your help!

Genevieve P.

Hi @K L

To add in criteria for the Status Column, you can reference the whole column, then use the OR function to specify the different criteria you're looking for.

Try this:

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

Let me know if you're seeing incorrect results with this!

Cheers,

Genevieve

Kelly Drake

You'd be looking for an IF(COUNTIF()) situation.....

I"m not sure what other things might impact this or if you wnated to limit it by a timeframe...

No timeframe:

IF(COUNTIF([Person Column Name]:[Person Column Name], [Person Column Name]@row) >=2,"Yes", "No)

WIth a timeframe of next two weeks:.... you might need to tinker with the dates section of htis one.

IF(COUNTIFS([Person Column Name]:[Person Column Name], [Person Column Name]@row, [Date Column Name]:[Date Column Name], <=TODAY()+7,[Date Column Name]:[Date Column Name], >=TODAY()-7) >=2,"Yes", "No)

Kelly Drake

**my date formula looks in from today forward seven days and backward 7 days.... if you're just looking forward two weeks use the TODAY+ segment and change the 7 to 14....

K L

Hi Kelly,

Thanks for your help. I'm getting UNPARSEABLE when I try two different ways. Do you happen to know what I'm doing wrong?

=IF(COUNTIF([Person Column Name]45:[Person Column Name]45, [Person Column Name]45) >=2,"Yes", "No)

=IF(COUNTIF([Person Column Name]:[Person Column Name], [Person Column Name]45) >=2,"Yes", "No)

K L

@Kelly Drake

Genevieve P.

Hi @K L

It looks like you haven't closed off the last quotation mark at the end, around the "No".

Try this:

=IF(COUNTIF([Person Column Name]:[Person Column Name], [Person Column Name]@row) >=2,"Yes", "No")

(Note, I also used @row instead of 45 so it only looks in the current row and doesn't have to search the sheet for where row 45 is).

Cheers!

Genevieve

K L

@Genevieve P Thanks so much that worked! Can you help me make it so that it searches in the next week? So is this person listed again in the next week?

K L

Or, the logic I'd be even more interested in: Is this person listed again in the upcoming Monday through Friday?

Genevieve P.

Hi @K L

Have you tried Kelly's formula above?

=IF(COUNTIFS([Person Column Name]:[Person Column Name], [Person Column Name]@row, [Date Column Name]:[Date Column Name], >=TODAY(7)) >=2,"Yes", "No")

Now, this will evaluate anything greater-than-or-equal-to the next 7 days from Today... so that does mean that if they are listed twice Today and Tomorrow this formula would pick it up, even if that's the same week. Keep in mind that on Monday it would then only look until the next Monday as well, not til next Friday.

Kelly suggested you could change this to be >=TODAY(14) instead of 7, but then on a Friday it would look to two Fridays from Today. Would that work for you?

>>Here's another Community post where I discussed checking specifically per-day in the next week, using Sunday - Saturday. It gets a little complicated as you will need to identify today's weekday, then adjust the formula to search through a different number of days based on Today.

Let me know if the above formula will work or not. If you still need help, it would perhaps be useful to understand a bit more about your process. For example, could you Move Rows that are in the past over to another archived sheet so you don't need to evaluate by date?

Cheers!

Genevieve

Genevieve P.

Hi @K L

Actually, in thinking about this a little more, you could use the WEEKNUMBER function to compare today's Week Number to next week's number and search for a duplicate there.

Try this instead:

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

I'll break down what it says...

Search in this range: [Date Column]:[Date Column],

to see if the WEEKNUMBER (in that range):

IFERROR(WEEKNUMBER(@cell),0)

(note, the IFERROR makes it read 0 if there is an error, like if it's looking at a blank cell)

Is the same as: =

Today's WEEKNUMBER, but plus one to make it next week: WEEKNUMBER(TODAY()) + 1)

Let me know if that solves your issue! 🙂

Genevieve

K L

@Genevieve P Thanks so much for your help! I got both formulas to work but I think what I'm seeing is that the yes/no is going based off of today's date and what I need it to do is calculate the date off of the end date on that row, something like, "Is the person on this row listed again in the next 7 days based on the end date?"

Genevieve P.

Hi @K L

Thank you for clarifying! This is no problem, you just need to adjust the criteria and replace TODAY with the cell reference of that end date.

Try this:

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

Does that make sense?

Genevieve

K L

@Genevieve P Thanks for connecting back. I think this is what I'm seeing:

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

This one with the +1 is working if the person is listed on a date that's only in the following week. But it's not working if that person is listed in the current week.

09/14/20   09/14/20 Trainer 1 No

09/15/20   09/17/20 Trainer 1 No

The one that works for that is changing the + 1 to be + 0:

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

Is there a way to say, is this person listed again in this week or the next week, from the end date?

Genevieve P.

Hi @K L

To clarify, you're looking to see if this person has a duplicate either in this current week OR next week, is that right? Whereas right now our formula is just looking to see if this person is listed again next week.

That's no problem! What we'll do is add together two COUNTIF formulas to find a total COUNT of that person:

One COUNT of that person in the current week, and one COUNT of that person in the next week. Then we'll add the COUNTS together, and if they're greater-than or equal-to 2, that means there's a second instance.

I'll break it down by each statement, then show you the full formula.

=IF(

COUNTIFS([Person Column Name]:[Person Column Name], [Person Column Name]@row, [Date Column]:[Date Column], IFERROR(WEEKNUMBER(@cell),0) = WEEKNUMBER([Date Column]@row))

+

COUNTIFS([Person Column Name]:[Person Column Name], [Person Column Name]@row, [Date Column]:[Date Column], IFERROR(WEEKNUMBER(@cell),0) = WEEKNUMBER([Date Column]@row) + 1)

>=2,

"Yes", "No")

Notice how the first count is just looking for the WeekNumber of the date in the Date Column? This count should always be at least 1, because the person is listed in that current row. Now, keep in mind that this is looking at the full week, so if the person in this row is listed earlier (ex. on Monday, and this row is Wednesday) it will count both of these so you will get a "Yes", even if that date is in the past.

Then the second COUNT is looking through next week, based on that Date Column. Put the two counts together for your total number, then embed that in an IF Statement.

FULL FORMULA:

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

Let me know if this makes sense, and if it accomplishes what you're looking to do. If this isn't right, it would be helpful to know exactly what it is you're looking to count, and why.

Cheers!

Genevieve

K L

@Genevieve P Awesome! Thanks for your patience, I think my ask has expanded from my original question.

I got this formula to work, can you take a look at where I put start and end dates, and make sure it looks right?

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

And then I see what you mean about it counting 1 as the row that is listing the person plus anything in the same week in the past. So since I only want to look into the future after the end date of that row, it will give a false yes for two people listed in the same week, but with the next week not having anyone.

09/08/20   09/08/20   Person 1 - Yes

09/09/20   09/11/20   Person 1 - Yes <---this should be 'No' since Person 1 doesn't have another date listed after this end date in the same week or in the follow week (9/14-9/18, M-F).

09/14/20   09/14/20   Person 2 - No

09/15/20   09/15/20   Person 3 - No

09/16/20   09/18/20   Person 4 - No

09/17/20   09/17/20   Person 5 - No

09/21/20   09/23/20   Person 6 - No

Is there a way to have it only look forward through the end of the following week? To give some context, a temporary license needs to be requested for each person. And then deactivated if that person doesn't have an upcoming date scheduled. So 5 days before Person 1 needs it on 9/8/2020, it will be activated and kept for the following date on 9/9-9/11. But after the end date on 9/11, it will need to be deactivated since that Person 1 is not scheduled again in the following week of 9/14-9/18.

K L

@Genevieve P Separately, but kind of similarly, can you help me get the opposite function for the Activation? So 5 days before the start date, a temporary license needs to be requested. If one is already active from the previous week, then I don't want to send a request to activate it. So I'd need a function that would be entered into its own column that is looking in the week of and week earlier than the start date, to see if that person already had a scheduled date. I also have a separate column already made that is recording Activated or Deactivated.

So I think the function would need to be something like: Did Person 2 listed on 9/22 have a previous date before in this week or in the previous week, where the license column shows "Activated"? If so, change the license column to say "Already Activated". I think that last part might be conditional formatting and I haven't set up one before.

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

09/15/20   09/15/20  Person 1

09/16/20   09/18/20  Person 1

09/17/20   09/17/20   Person 2 - Activated

09/18/20   09/18/20   Person 1

09/21/20   09/21/20   Person 1

09/22/20   09/22/20   Person 2 - This would say 'Yes' because Person 2 had a scheduled date in the previous week and their license is still activated.

09/23/20   09/23/20   Person 1

09/24/20   09/24/20   Person 1

09/25/20   09/25/20   Person 1