shieldsco
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") )
=IF(AND(G2>=Date 10/1/2001,G2<=Date 9/30/2002,"FY2002","None")
Can you attach your workbook please?
Try this:
=IF(AND(G2>=DATE(2001, 10, 1),G2<=DATE(2002, 9, 30)),"FY2002","None")
»bp
=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>=DATE(2001; 10; 1);G2<=DATE(2002; 9; 30));"FY2002";"None")
=IF(AND(G2>=DATEVALUE("1/1 0/2001"),G 2<=DATEVAL UE("30/9/2 002")),"FY 2002","Non e")
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
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
ASKER
Try =IF(AND(G2>=DATE(2001,1,10 ),G2<=DATE (2002,9,30 )),"FY2002 ","None")
Steve
Steve
Sorry for duplicate, just realised I hadn't hit submit earlier!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
Bill calculating incorrectly
FY are Between 10/1/xxxx and 9/30/xxxx
3/5/2015 1048 IL Review and Close FY2015 sb FY2016
FY are Between 10/1/xxxx and 9/30/xxxx
3/5/2015 1048 IL Review and Close FY2015 sb FY2016
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
Steve
ASKER
My bad.... you are right. Thanks
ASKER
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
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
ASKER
That's fine
Thanks.
»bp
»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
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
Cheers,
Bill