Tricky Excel Formula

[Quinton]

Hello I need the following formula from someone clever!

example data below

column a column b column c
26-05-11 10-03-11 1
17-06-11 23-06-11 1
13-02-11 22-06-11 0

Column C is the formula I require. It is counting 1 or 0 if the following applies:

If column B's date happens before column A's date then count 1
If column B's date happens within the same month of Column A's Month then count 1
If Column B's date happens in the following month or any forther months (example row 3) on Column A's date then count 0

Good luck!

Thanks

Quinton

 

[jmthompson]

what should happen if none of these options apply?

 

[cwunderlich]

try =IF(OR(B1<A1,MONTH(B1)=MONTH(A1)),1,0)

 

[jmthompson]

nevermind, try
weird, won't show my formula-
=IF(OR(B1<(A1),<a1,month(b1)=<a1,month(b1)
MONTH(B1)=MONTH(A1)),1,0)</a1,month(b1)=<a1,month(b1)

 

[VoG]

Try

Excel Workbook
	A	B	C
1	26/05/2011	10/03/2011	1
2	17/06/2011	23/06/2011	1
3	13/02/2011	22/06/2011	0
Sheet5

 

[GlennUK]

How about:

Excel Workbook
	A	B	C
1	26/05/2011	10/03/2011	1
2	17/06/2011	23/06/2011	1
3	13/02/2011	22/06/2011	0
Sheet1

... you may need to tick the Analysis Toolpak add-in, depending on which version of Excel you have.

 

[Quinton]

Many thanks for your help.

I don't think there is another option? But if there is then count 0

many thanks

 

[machopicho]

=if(or(month(a1)=month(b1),(month(a1)>month(b1))),"1","0")

 

[Quinton]

Many thanks Glen - it is working perfectly!

Kind regards,

 

[GlennUK]

Many thanks Glen - it is working perfectly!

Kind regards,

That's great!