• Status: Solved
• Priority: High
• Security: Public
• Views: 85

# 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"))
0
shieldsco
• 7
• 6
• 5
• +3
2 Solutions

Older than dirtCommented:
0

Commented:
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

Commented:
Typo in Steves second version, should be:

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

»bp
0

Commented:
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

Commented:
@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

Commented:
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

Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.