IF Function with Dates

[Bagia]

Hello,

I hope someone can help me. I would like to create an IF function for dates
in my worksheet. I have a Start Date column and End Date column. If the
date in the Start Date and End Date falls within the Fiscal Year(06), then I
want to display a 1, otherwise a 0(zero).

A1=7/01/06 (Start Date); B1=6/30/10 (End Date)

FY06 is from 7/1/2005 to 6/30/2006

What I have in C1 is =IF(AND(A1>=7/1/2005,B1<=6/30/2006),1,0)

My result for FY06 is 0, but that's not correct because the End Date is not
until 12/31/2010, so it falls within the FY06. I want this formula to do the
same for FY07, FY08, and FY09.

Can someone help?

Thanks in Advance.

 

[Dave Peterson]

=IF(AND(A1>=date(2005,7,1),B1<=date(2006,6,30)),1,0)
or even
=--(AND(A1>=date(2005,7,1),B1<=date(2006,6,30)))
or
=(A1>=date(2005,7,1))*(B1<=date(2006,6,30))

The first - changes true/false to -1/0. The second - changes it 1/0.

Multiplication (true*true=1) does the same thing.

 

[Bagia]

Hi Dave,
I used the first option and my result comes back with 0(zero) but it should
be 1, because the end date is 06/30/2010 so the Fiscal Year for 06 falls
within the Date Range.

FY06 date range is from 7/1/2005 to 6/30/2006
FY07 ......7/1/2006 to 6/30/2007
FY08 ......7/1/2007 to 6/30/2008
FY09 ......7/1/2008 to 6/30/2009

FY06=1
FY07=1
FY08=1
FY09=1

I also have A2=09/12/05 (start date)
B2=06/30/06 (end date)

So answer should be:
FY06=1
FY07=0
FY08=0
FY09=0

 

[Bagia]

My goal to display a one if the Fiscal Year falls within the Start Date and
End Date, then it should have a 1, otherwise the result should be 0.

So if my start date is 10/15/07 and end date is 07/01/08, my result should be:

FY06=0
FY07=0
FY08=1
FY09=1

Appreciate all the help!

 

[Fred Smith]

Show us the formula you used.

Regards,
Fred

My goal to display a one if the Fiscal Year falls within the Start Date
and
End Date, then it should have a 1, otherwise the result should be 0.

So if my start date is 10/15/07 and end date is 07/01/08, my result should
be:

FY06=0
FY07=0
FY08=1
FY09=1

Appreciate all the help!

 

[Bagia]

Thanks your email Fred. Below is the formula I used

=IF(AND(A1>=date(2005,7,1),B1<=date(2006,6,30)),1,0)

 

[Fred Smith]

There's no reason that formula shouldn't work. What is in A1 and B1?

Regards,
Fred

 

[Dave Peterson]

Instead of retyping the formula, copy it from the formula bar and paste it into
your message.

It's too easy to make a typing error -- even one that corrects the actual error
in your message!

 

[Vijay]

Suppose Start date is in A1, end date is in B1 and FY start date is in C1
and FY end date is in D1,
Then put this formula in C1
=IF(OR(AND(D1>=A1,D1<=B1),AND(E1>=A1,E1<=B1)),1,0)

Vijay

 

[Vijay]

:
Sorry, pl read it like this,

Suppose Start date is in A1, end date is in B1 and FY start date is in D1
and FY end date is in E1,
Then put this formula in C1
=IF(OR(AND(D1>=A1,D1<=B1),AND(E1>=A1,E1<=B1)),1,0)

Vijay