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

kay89

New Member
Joined
Jan 26, 2021
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
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.

Please help me figure this out..

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

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,152
Office Version
  1. 365
Platform
  1. Windows

kay89

New Member
Joined
Jan 26, 2021
Messages
4
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jul 14, 2008
Messages
3,152
Office Version
  1. 365
Platform
  1. Windows
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
 
Solution

kay89

New Member
Joined
Jan 26, 2021
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
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!!
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top