-2

Our financial year runs from the first of March to the last day of February. I need a formula in Excel that tells me if the financial year has a leap day (the 29th of February) or not.

For example 03/01/2015 falls in the Financial Year 2015/2016, and ends at 02/29/2016, so it has a Leap Day. However 03/01/2016 falls in the Financial Year 2016/2017, which does not contain a leap day.

I want to be able to enter any date and determine if the fiscal year it falls within contains a leap day.

Hennes
  • 64,768
  • 7
  • 111
  • 168
Sumeet
  • 1
  • 1
  • 1
  • 3
    What language/environment are you using? Excel? – Dmitry Grigoryev May 26 '15 at 14:30
  • When is your financial year-end? It varies from country to country. – Chenmunka May 26 '15 at 14:43
  • Edit your question and mention the environment or use the appropriate tag for it. Just tagging this "windows" means nothing at all. – Karan May 26 '15 at 18:39
  • What I want is like if we enter any date which include 02/29/16 in that financial year then it should consider as a Leap Year. So, if we select 02/01/2016 then also it will consider as a Leap Year but if we select 03/01/2016 which is after 02/29/16 then it won't consider as a Leap Year. – Sumeet May 27 '15 at 11:15

1 Answers1

2

Microsoft has a KB article about this subject. The formula they suggest to dertemine if a year is a leap year is:

=IF(OR(MOD(A1,400)=0,AND(MOD(A1,4)=0,MOD(A1,100)<>0)),"Leap Year", "NOT a Leap Year")

As a side-note: the date system of Microsoft Excel intentionally has an error by regarding the year 1900 as a leap year. See this SE Q&A for an excellent answer about this.

If I apply this to your question, what you are actually doing is putting the first of march in the next year. So we need two adjustments to the above formula:

  • Your data contains a date instead of only a year, so A1 becomes YEAR(A1).
  • We add 306 days to A1. By that we count the first of march (and forward) to the next year.

The resulting formula is:

=IF(OR(MOD(YEAR(A1+306);400)=0;AND(MOD(YEAR(A1+306);4)=0;MOD(YEAR(A1+306);100)<>0));"Financial Year has a Leap Day"; "Financial Year has NO Leap Day")

This formula reports (date in DD/MM/YYYY):

01/01/2014 --> Financial Year has NO Leap Day
01/01/2016 --> Financial Year has a Leap Day
29/02/2016 --> Financial Year has a Leap Day
01/03/2016 --> Financial Year has NO Leap Day
Community
  • 1
agtoever
  • 6,262
  • 1
  • 24
  • 37
  • If we use the above formula, then if we select 04/01/2015 then it should also consider as Leap Year as financial year end contains date 02/29/2016. So, I think we need to change the formula. Like if the date is more than 03/01/2016 then it won't consider as Leap Year. – Sumeet May 26 '15 at 14:55
  • How do you call the financial year that starts at 3/1/2016? Is it 2016 or 2017 or 2016/2017? – agtoever May 26 '15 at 15:00
  • What I want is like if we enter any date which include 02/29/16 in that financial year then it should consider as a Leap Year. So, if we select 02/01/2016 then also it will consider as a Leap Year but if we select 03/01/2016 which is after 02/29/16 then it won't consider as a Leap Year. – Sumeet May 26 '15 at 15:13