IF / OR formula

**moster** · 02-09-2010, 03:55 PM

could any help me with this formula, summary below of what i am trying to do.

A B C D E F
1 Name StartDate EndDate Jan-10 Feb-10 March-10
2 Tom 3/1/10 31/12/10 1 1 1
3 **** 2/2/10 31/10/10 0 1 1
4 Harry 15/3/10 1/12/10 0 0 0

Take D2
it looks at d1 and see's if it falls between b2 and c2 if yes then shows 1 if not then shows 0

any help would be greatly appreciated.

**NBVC** · 02-09-2010, 04:12 PM

Try:

=--AND(D$1>=$B2,D$1<=$C2)

copied down and across

**moster** · 02-09-2010, 04:28 PM

wow that was quick

seems to work fine but i would expect to see a 1 in feb 10 but the ones' dont start until march. (for row 3)

any ideas?

**NBVC** · 02-09-2010, 04:34 PM

Are your dates in columns B and C entered properly as dates...

Select column B and go to Data|Text to Columns and skip to 3rd window. Select Date from the column data format section and DMY from dropdown next to it.

Repeat for column C.

Does that fix it?

**moster** · 02-09-2010, 04:38 PM

nope done that still the same result, 0 in the feb column

**NBVC** · 02-09-2010, 04:40 PM

Here it is working for me

**moster** · 02-09-2010, 04:51 PM

erm your row 4 'cell f4' should show 1 as the start date lands in march

**smiler99** · 02-09-2010, 05:08 PM

this adaption takes into consideration any day within the month and converts your start/end dates to the first of the month, therefore being abl to compare to the month/year format of the columns

=--(AND(V$8>=DATE(YEAR($D9),MONTH($D9),1),V$8<=DATE(YEAR($E9),MONTH($E9),1)))

**NBVC** · 02-09-2010, 05:12 PM

Your sample shows 0 in that spot..

Maybe try:

=--AND(EOMONTH($B2,-1)+1<=D$1,EOMONTH(D$1,0)<=$C2)

copied across the table.

Make your you have Analysis Toolpak addin installed (Tools|Addins)