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?
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,946
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
75,946
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,671
Messages
5,549,344
Members
410,910
Latest member
DessertDiva
Top