COUNT DATES THAT ARE WITHIN 1 DAY

[San Antonio Heat]

Hi all,
I have dates in two columns, sheet1 A and B

On Sheet 2, I would like to count all the days that are equal to or within a day (+1)
Example Sheet 1
A1: 3/1/21 B1: 3/2/21
A2: 3/1/21 B2: 3/15/21
A3: 3/2/21 B3: 3/2/21
A4: 3/2/21 B4: 3/3/21
A5: 3/3/21 B5: 3/22/21

Sheet 2
A1 (formula) should equal 3

Thanks all.

 

[hajiali]

equal to or within a day (+) to what date?

 

[jtakw]

Hi,

Try this:

Book3.xlsx
	A	B	C	D
1	3/1/2021	3/2/2021		3
2	3/1/2021	3/15/2021
3	3/2/2021	3/2/2021
4	3/2/2021	3/3/2021
5	3/3/2021	3/22/2021
Sheet854

Cell Formulas
Range	Formula
D1	D1	=SUMPRODUCT(--(B1:B5-1<=A1:A5))

If you may have dates in column A that is Later than corresponding column B, will need mod to formula.

 

[Celly]

@jtakw beat me by a few minutes but I can deal with the reverse date order

=SUMPRODUCT(--(ABS(B1:B5-A1:A5)<=1))

 

[San Antonio Heat]

Hi,

Try this:

Book3.xlsx
	A	B	C	D
1	3/1/2021	3/2/2021		3
2	3/1/2021	3/15/2021
3	3/2/2021	3/2/2021
4	3/2/2021	3/3/2021
5	3/3/2021	3/22/2021
Sheet854

Cell Formulas
Range	Formula
D1	D1	=SUMPRODUCT(--(B1:B5-1<=A1:A5))

If you may have dates in column A that is Later than corresponding column B, will need mod to formula.

Thanks Jtakw, that worked like a charm.

 

[San Antonio Heat]

@jtakw beat me by a few minutes but I can deal with the reverse date order

=SUMPRODUCT(--(ABS(B1:B5-A1:A5)<=1))

what does the - - mean in the formula if you don't mind me asking?

 

[Celly]

what does the - - mean in the formula if you don't mind me asking?

It converts the boolean to an integer

 

[jtakw]

You're welcome, thanks for the feedback.

The double Unary ( -- ), converts TRUE, FALSE to 1 and 0

@Celly, if OP replys that there may be reverse ordered dates, then you would have already beat me to it.