IF formula - combining with AND?

davejago

Board Regular
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?

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Try this formula.

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

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.

Maybe try this:

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

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?

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.

Maybe this?

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

Maybe this?

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

=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

Replies
0
Views
268
Replies
1
Views
88
Replies
8
Views
412
Replies
7
Views
208
Replies
1
Views
131

1,219,798
Messages
6,150,318
Members
450,951
Latest member
kh198

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back