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

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
Asked:
shieldsco
  • 7
  • 6
  • 5
  • +3
2 Solutions
 
Martin LissOlder than dirtCommented:
Can you attach your workbook please?
0
 
Bill PrewCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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 PrewCommented:
Typo in Steves second version, should be:

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


»bp
0
 
Bill PrewCommented:
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 PrewCommented:
@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 PrewCommented:
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 PrewCommented:
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.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 7
  • 6
  • 5
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now