dates formula

[xxsalahxx]

Hello I have a spreadsheet showing many dates approx 20,000 that show number of tests taken.

I would like to find out the number of tests taken in column A (dates) match that of all the dates in column B (dates) or were held 1 day later. Example Below dates would show as 2 dates matching, is there a piece of analysis to do this? any help would be appreciated, thank you

[TABLE="width: 198"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD="align: right"]01/04/2018[/TD]
[TD="align: right"]01/04/2018 [/TD]
[/TR]
[TR]
[TD="align: right"]02/04/2018[/TD]
[TD="align: right"]03/04/2018[/TD]
[/TR]
[TR]
[TD="align: right"]01/05/2018[/TD]
[TD="align: right"]06/04/2018[/TD]
[/TR]
[TR]
[TD="align: right"]01/06/2018[/TD]
[TD="align: right"]
23/07/2018

[/TD]
[/TR]
</tbody>[/TABLE]

 

[jproffer]

My date format is different than yours, but do you just mean something like this?

Book1
	A	B	C
1	6/17/2018	6/18/2018	Same or +/-1 Day
2	6/18/2018	6/19/2018	Same or +/-1 Day
3	6/19/2018	6/20/2018	Same or +/-1 Day
4	6/20/2018	6/21/2018	Same or +/-1 Day
5	6/21/2018	6/21/2018	Same or +/-1 Day
6	6/22/2018	6/22/2018	Same or +/-1 Day
7	6/23/2018	6/23/2018	Same or +/-1 Day
8	6/24/2018	7/2/2018	Beyond 1 day
9	6/25/2018	7/3/2018	Beyond 1 day
10	6/26/2018	7/4/2018	Beyond 1 day
11	6/27/2018	7/5/2018	Beyond 1 day
12	6/28/2018	7/6/2018	Beyond 1 day
13	6/29/2018	7/7/2018	Beyond 1 day
Sheet1

Cell Formulas
Range	Formula
C1	=IF(OR(A1=B1,(ABS(A1-B1)=1)),"Same or +/-1 Day","Beyond 1 day")

 

[xxsalahxx]

My date format is different than yours, but do you just mean something like this?

	A	B	C
Same or +/-1 Day
Same or +/-1 Day
Same or +/-1 Day
Same or +/-1 Day
Same or +/-1 Day
Same or +/-1 Day
Same or +/-1 Day
Beyond 1 day
Beyond 1 day
Beyond 1 day
Beyond 1 day
Beyond 1 day
Beyond 1 day

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]6/17/2018[/TD]
[TD="align: right"]6/18/2018[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]6/18/2018[/TD]
[TD="align: right"]6/19/2018[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]6/19/2018[/TD]
[TD="align: right"]6/20/2018[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]6/20/2018[/TD]
[TD="align: right"]6/21/2018[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]6/21/2018[/TD]
[TD="align: right"]6/21/2018[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]6/22/2018[/TD]
[TD="align: right"]6/22/2018[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]6/23/2018[/TD]
[TD="align: right"]6/23/2018[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]6/24/2018[/TD]
[TD="align: right"]7/2/2018[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]6/25/2018[/TD]
[TD="align: right"]7/3/2018[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]6/26/2018[/TD]
[TD="align: right"]7/4/2018[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]6/27/2018[/TD]
[TD="align: right"]7/5/2018[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]6/28/2018[/TD]
[TD="align: right"]7/6/2018[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]6/29/2018[/TD]
[TD="align: right"]7/7/2018[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C1[/TH]
[TD="align: left"]=IF(OR(A1=B1,(ABS(A1-B1)=1)),"Same or +/-1 Day","Beyond 1 day")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Hello yes I was wondering if there was a piece of analysis that I can put into one cell to count all at the same time? thank you.

 

[mse330]

Try the below formula in cell C1 then press Ctrl+Shift+Enter (CSE) to generate the curly braces (don't type the {}) … The formula will give you 1 or 0 which indicates if the date is matching date (1) or not (0)

{=MAX(IF(OR(A1=B:B,A1=B:B+1),1,0))}

 

[xxsalahxx]

Try the below formula in cell C1 then press Ctrl+Shift+Enter (CSE) to generate the curly braces (don't type the {}) … The formula will give you 1 or 0 which indicates if the date is matching date (1) or not (0)

{=MAX(IF(OR(A1=B:B,A1=B:B+1),1,0))}

thank you both ways seem to work, thanks again

 

[mse330]

Glad we could help. You are welcome