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"))
shieldscoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Martin LissOlder than dirtCommented:
Can you attach your workbook please?
0
Bill PrewIT / Software Engineering ConsultantCommented:
Try this:

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


»bp
0
pcelbaCommented:
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

MacroShadowCommented:
=IF(AND(G2>=DATEVALUE("1/10/2001"),G2<=DATEVALUE("30/9/2002")),"FY2002","None")
0
shieldscoAuthor 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
shieldscoAuthor Commented:
0
Steve KnightIT ConsultancyCommented:
Try =IF(AND(G2>=DATE(2001,1,10),G2<=DATE(2002,9,30)),"FY2002","None")

Steve
0
Steve KnightIT ConsultancyCommented:
Sorry for duplicate, just realised I hadn't hit submit earlier!
1
Steve KnightIT 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)))

Open in new window


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
shieldscoAuthor 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
shieldscoAuthor 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
Bill PrewIT / 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

Your issues matter to us.

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

Start your 7-day free trial
Bill PrewIT / Software Engineering ConsultantCommented:
This:

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

contradicts:

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
Steve KnightIT 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
shieldscoAuthor Commented:
My bad.... you are right. Thanks
0
shieldscoAuthor Commented:
Thanks
0
Bill PrewIT / 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
shieldscoAuthor Commented:
That's fine
0
Bill PrewIT / Software Engineering ConsultantCommented:
Thanks.


»bp
0
Steve KnightIT 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
Bill PrewIT / 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.