IF formula - combining with AND?

davejago

Board Regular
Joined
Apr 29, 2005
Messages
133
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

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,351
Office Version
  1. 365
Platform
  1. Windows
Try this formula.

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

davejago

Board Regular
Joined
Apr 29, 2005
Messages
133
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

Well-known Member
Joined
May 19, 2002
Messages
2,916
Maybe try this:

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

davejago

Board Regular
Joined
Apr 29, 2005
Messages
133

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,351
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
May 19, 2002
Messages
2,916

ADVERTISEMENT

Maybe this?

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

Seti

Well-known Member
Joined
May 19, 2002
Messages
2,916
Maybe this?

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

tony0682

Board Regular
Joined
May 19, 2005
Messages
164
=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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,126
Messages
5,857,522
Members
431,883
Latest member
Hien

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top