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

01/04/2018	01/04/2018
02/04/2018	03/04/2018
01/05/2018	06/04/2018
01/06/2018	23/07/2018

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[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
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

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula C1 =IF(OR(A1=B1,(ABS(A1-B1)=1)),"Same or +/-1 Day","Beyond 1 day") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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