IF formula - combining with AND?

[davejago]

I have 2000 rows of dates - all in the format mm.dd.yyyy, ranging from 01.01.1995 to 01.01.2007. I need to label them into their appropriate Financial Years.

e.g. FY96 04.01.1995 to 03.31.1996
FY97 04.01.1996 to 03.31.1997

Should I just use a combination of IF and AND formulas to help me do this? If so, does anyone know how?

 

[Norie]

Try this formula.

="FY" &(YEAR(A1)+(MONTH(A1)>3))

 

[davejago]

I get a VALUE error! The IF statement works, I just dont know how to combine it with the AND function to make a mammoth formula.

 

[Seti]

Maybe try this:

="FY"&(RIGHT(A1,2)+(--LEFT(A1,2)>3))

 

[davejago]

That works for the FY's in the 90's...but when you get to the 2000's it shows FY1 or FY2. It needs to show FY01 FY02 etc. Any ideas?

 

[Norie]

davejago

The formula I posted should work if you have true date values.

If you really are using . as a seperator rather than / then they probably aren't true date values.

 

[Seti]

Maybe this?

="FY"&TEXT((RIGHT(A1,2)+(--LEFT(A1,2)>3)),"00")

 

[Seti]

Maybe this?

="FY"&TEXT((RIGHT(A1,2)+(--LEFT(A1,2)>3)),"00")

 

[tony0682]

=IF(AND(A1>38442, A1<38808),"FY05"," ")

38442 = 3/31/2005
38808 = 4/1/2006

Using it this way would require you to put nested ifs for the other years. Dates are converted to their number format in the formula