If the same caller dialed again within a period of N days

kay89

New Member
Hello,

My worksheet has a column of phone numbers, and another column with the date that this number dialed.
I'd like to know please for every phone number - if this phone number has dialed again within a period of 3 days.

if its true - return 1, if not - return 0.

My data cannot be pre-sorted by first to last date or stuff like that, and i cant use VBA nor pivot tables.

Thanks a lot!

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

kay89

New Member

A formula way.

4.xlsx
ABC
1NumberDateDate w/in 3 days
2111-11111/26/2021FALSE
3111-11111/31/2021FALSE
4111-11112/5/2021FALSE
5111-11112/10/2021FALSE
6111-11112/15/2021FALSE
7111-11112/20/2021FALSE
8111-11112/25/2021FALSE
9111-11113/2/2021FALSE
10111-11113/7/2021TRUE
11111-11113/10/2021TRUE
12111-11113/15/2021FALSE
13222-22223/17/2021FALSE
Sheet3
Cell Formulas
RangeFormula
C2:C13C2=SUMPRODUCT(--(ABS(B2-\$B\$2:\$B\$13)<=3)*(\$A\$2:\$A\$13=A2))-1>0
B3:B10,B12B3=B2+5
B11B11=B10+3
B13B13=B12+2
Brilliant! Thanks a lot

But.. what if i would have another data column that contains the Subject of the call (text), and now i want to change the calculated formula a bit:

I would like to check if a certain caller called within a period of 3 days, just as before, but- only if he called for the same reason (same subject).
I mean, If he called within a period of 3 days but for a different subject- let it return false.

Thank you very much!!!

lrobbo314

Well-known Member
Try this.

4.xlsx
ABCDE
1NumberDateCallerReasonDate w/in 3 days
2111-11111/26/2021AWFALSE
3111-11111/31/2021AWFALSE
4111-11112/5/2021AWFALSE
5111-11112/10/2021AWFALSE
6111-11112/15/2021AWFALSE
7111-11112/20/2021AWFALSE
8111-11112/25/2021AWFALSE
9111-11113/2/2021AWFALSE
10111-11113/7/2021AWTRUE
11111-11113/10/2021AWTRUE
12111-11113/13/2021AWTRUE
13222-22223/15/2021BXFALSE
14222-22221/26/2021BXTRUE
15222-22221/28/2021BXTRUE
16222-22221/31/2021BYFALSE
Sheet3
Cell Formulas
RangeFormula
E2:E16E2=SUMPRODUCT(--(ABS(B2-\$B\$2:\$B\$16)<=3)*(\$A\$2:\$A\$16=A2)*(\$C\$2:\$C\$16=C2)*(\$D\$2:\$D\$16=D2))-1>0

kay89

New Member
Try this.

4.xlsx
ABCDE
1NumberDateCallerReasonDate w/in 3 days
2111-11111/26/2021AWFALSE
3111-11111/31/2021AWFALSE
4111-11112/5/2021AWFALSE
5111-11112/10/2021AWFALSE
6111-11112/15/2021AWFALSE
7111-11112/20/2021AWFALSE
8111-11112/25/2021AWFALSE
9111-11113/2/2021AWFALSE
10111-11113/7/2021AWTRUE
11111-11113/10/2021AWTRUE
12111-11113/13/2021AWTRUE
13222-22223/15/2021BXFALSE
14222-22221/26/2021BXTRUE
15222-22221/28/2021BXTRUE
16222-22221/31/2021BYFALSE
Sheet3
Cell Formulas
RangeFormula
E2:E16E2=SUMPRODUCT(--(ABS(B2-\$B\$2:\$B\$16)<=3)*(\$A\$2:\$A\$16=A2)*(\$C\$2:\$C\$16=C2)*(\$D\$2:\$D\$16=D2))-1>0
Perfect.
Thanks a lot!!

Replies
0
Views
78
Replies
1
Views
767
Replies
1
Views
95
Replies
1
Views
427
Replies
0
Views
106

1,130,163
Messages
5,640,519
Members
417,150
Latest member
cdguinn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back