Link to home
Start Free TrialLog in
Avatar of shieldsco
shieldscoFlag for United States of America

asked on

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"))
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Can you attach your workbook please?
Avatar of Bill Prew
Bill Prew

Try this:

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


»bp
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")
=IF(AND(G2>=DATEVALUE("1/10/2001"),G2<=DATEVALUE("30/9/2002")),"FY2002","None")
Avatar of shieldsco

ASKER

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
Try =IF(AND(G2>=DATE(2001,1,10),G2<=DATE(2002,9,30)),"FY2002","None")

Steve
Sorry for duplicate, just realised I hadn't hit submit earlier!
SOLUTION
Avatar of Steve Knight
Steve Knight
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Bill calculating incorrectly

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

3/5/2015            1048      IL            Review and Close      FY2015 sb FY2016
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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
My bad.... you are right. Thanks
Thanks
@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
That's fine
Thanks.


»bp
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
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