-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
  • 65,804
  • 7
  • 115
  • 169
Sumeet
  • 1

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,402