# IF statement with two dates

1. ## IF statement with two dates

I am trying to write an IF statement to tell me which fiscal year the
transcation occurred in. My formula is:

=IF((AND(I2>="11/1/2004",I2<="10/31/2005")),"FY
2005",(IF((AND(I2>="11/1/2005",I2<="10/31/2006")),"FY 2006", "ERROR")))

I2 is the cell which contains the date of the transcation.

Dates between 11/1/2004 and 10/31/2005 are in FY 2005 and dates between
11/1/2005 adn 10/31/2006 are in FY 2006.

I appreciate any help.

2. ## Re: IF statement with two dates

You need to use the DATE() function to express the dates:
=IF((AND(I2>=DATE(2004,11,1),I2<=DATE(2005,10,31)),"FY 2005"... etc

If the rule is that the fiscal year always runs from 1 Nov to 31 Oct,
then a general formula with for the fiscal year is:

=YEAR(I2)+IF(MONTH(I2)>10,1,0)

jbormann wrote:
> I am trying to write an IF statement to tell me which fiscal year the
> transcation occurred in. My formula is:
>
> =IF((AND(I2>="11/1/2004",I2<="10/31/2005")),"FY
> 2005",(IF((AND(I2>="11/1/2005",I2<="10/31/2006")),"FY 2006", "ERROR")))
>
> I2 is the cell which contains the date of the transcation.
>
> Dates between 11/1/2004 and 10/31/2005 are in FY 2005 and dates between
> 11/1/2005 adn 10/31/2006 are in FY 2006.
>
> I appreciate any help.

3. Would the formula work for you as

=IF(AND(I2>DATE(2004,10,31),I2<DATE(2005,11,1)),"FY 2005",IF(AND(I2>DATE(2005,10,31),I2<DATE(2006,11,1)),"FY 2006","Error"))

Hope that helps

Carl

Originally Posted by jbormann
I am trying to write an IF statement to tell me which fiscal year the
transcation occurred in. My formula is:

=IF((AND(I2>="11/1/2004",I2<="10/31/2005")),"FY
2005",(IF((AND(I2>="11/1/2005",I2<="10/31/2006")),"FY 2006", "ERROR")))

I2 is the cell which contains the date of the transcation.

Dates between 11/1/2004 and 10/31/2005 are in FY 2005 and dates between
11/1/2005 adn 10/31/2006 are in FY 2006.

I appreciate any help.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1