Excel If Statement Between Dates

I'm tying to calculate fiscal years between dates using the formula below with an error. Thoughts

=IF(AND(G2>=Date 10/1/2001,G2<=Date 9/30/2002,"FY2002","None"))
Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Older than dirtCommented:
0
IT / Software Engineering ConsultantCommented:
Try this:

=IF(AND(G2>=DATE(2001, 10, 1),G2<=DATE(2002, 9, 30)),"FY2002","None")

»bp
0
Commented:
Excel requires semicolons as operand separators so the correct formula is

=IF(AND(G2>=DATE(2001; 10; 1);G2<=DATE(2002; 9; 30));"FY2002";"None")
0
Commented:
=IF(AND(G2>=DATEVALUE("1/10/2001"),G2<=DATEVALUE("30/9/2002")),"FY2002","None")
0
Author Commented:
Martin attached spreadsheet. below are the fiscal years

FY2002 10/1/2001 - 9/30/2002
FY2015  10/1/2014 - 9/30/2015
FY2016  10/1/2015 -9/30/2016
FY2017   10/1/2016 - 9/30/2017
FY2018  10/1/2017 - 9/30/2018
0
Author Commented:
0
IT ConsultancyCommented:
Try =IF(AND(G2>=DATE(2001,1,10),G2<=DATE(2002,9,30)),"FY2002","None")

Steve
0
IT ConsultancyCommented:
Sorry for duplicate, just realised I hadn't hit submit earlier!
1
IT ConsultancyCommented:
Actually try this for a generic formula:

``````=IF(OR(MONTH(G2)=10,MONTH(G2)=11,MONTH(G2)=12),"FY"&YEAR(G2)+1,"FY"&YEAR(G2))
or
=IF(G2="","",IF(MONTH(G2)>=10),"FY"&YEAR(G2)+1,"FY"&YEAR(G2)))
``````

So if "g2" is in October, November. December then it is in Financial year of the NEXT year, otherwise it is the same year

Steve
0
Author Commented:
Steve - not calculating FY correctly

9/19/2016            484      TX      596.8      Awaiting Assignment      FY2017 s/b FY2016 cutoff is 9/30
10/24/2016            449      SC      1312.76      Awaiting Assignment      FY2017
0
Author Commented:
Bill calculating incorrectly

FY are Between 10/1/xxxx and 9/30/xxxx

3/5/2015            1048      IL            Review and Close      FY2015 sb FY2016
0
IT / Software Engineering ConsultantCommented:
Typo in Steves second version, should be:

=IF(G2="","",IF(MONTH(G2)>=10,"FY"&YEAR(G2)+1,"FY"&YEAR(G2)))

»bp
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

IT / Software Engineering ConsultantCommented:
This:

3/5/2015            1048      IL            Review and Close      FY2015 sb FY2016

FY2002 10/1/2001 - 9/30/2002
FY2015  10/1/2014 - 9/30/2015
FY2016  10/1/2015 -9/30/2016
FY2017   10/1/2016 - 9/30/2017
FY2018  10/1/2017 - 9/30/2018

»bp
0
IT ConsultancyCommented:
Exactly... 5th March 2015 (to save using the bizarre mm/dd/yyyy you lot insist on using!) is surely either in year ending 30-Sep-2015 and therefore FY2015 or if you use the start of the year then after 1-Oct-2014 so in FY2014.  Can't be FY2016 though?

Steve
0
Author Commented:
My bad.... you are right. Thanks
0
Author Commented:
Thanks
0
IT / Software Engineering ConsultantCommented:
@shieldsco,

With your permission I'd like to reopen the question so you can select multiple solutions.  At a minimum, the one you currently selected and his last post that was almost there except for a single misplaced paren.  He deserves most of the credit if you went with my correction, so an approach could be to select my correction as the solution, his related post as an assist, and actually allocate more points to him in the close process.  Let me know if you agree.

»bp
0
Author Commented:
That's fine
0
IT / Software Engineering ConsultantCommented:
Thanks.

»bp
0
IT ConsultancyCommented:
Thanks all... and hello Bill, not had much chance to be on EE recently but saw this popup earlier when there were no comments but my phone didn't submit annoyingly as I found out later.

Don't worry whatever points up as, we all had the same ideas.

Steve
0
IT / Software Engineering ConsultantCommented:
Good to hear from you Steve, I need to drop you an email and see how things are, adding to my daily todo list...

Cheers,
Bill
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.