# Need a formula for Leap Year

I need a formula in which, if the date is equal to or greater than 03/01/2015 and less than or equal to 02/29/2016 then I get the result as "Leap Year" or "No Leap Year".

Thanks! Sumeet

• Jun 10, 2015 at 17:51
• @Sumeet - What have you tried? Jun 10, 2015 at 17:52
• 2015 is not a leap year. 2016 is a leap year. Jun 10, 2015 at 17:52
• So specifically, you don't care about dates outside that very, very narrow range? Jun 10, 2015 at 17:57
• I need a formula in which there were 2 dates. First one is Term Start Date and second is Term End Date. Now, if I enter 03/01/2015 in Term Start Date and End Date would be anything then the answer would be "Leap Year" as if the Term Start Date is 3/1/2015 then financial end date would be 02/29/2016, so the answer should be "leap year" but if the Term Start Date is after 03/01/2016 then it won't consider as a Leap Year as 02/29/16 is before that date. Jun 10, 2015 at 18:05

Another way is that the date function in Excel will increment the month if the day is out of range so `=date(2015,1,32)`(32nd of January) returns Feb 1, 2015.

So if you have a year in A1 this formula will return if it is a leap year or not.

``````=IF(DATE(A1,2,29)=DATE(A1,3,1),"No Leap Year","Leap Year")
``````

If it is not a leap year the program rounds February 29 to March 1 and is equal (no leap year).

Edit based on your comment: If your date is in A1 and you want to find if there is a Feb 29th within the next year

``````=if((date(year(A1)+1,month(A1),day(A1))-A1)=366,"Leap Year","No Leap Year")
``````

Increment the year by 1, subtract the original and check if there are 366 days.

• That's a clever solution! Jun 11, 2015 at 11:29
• You could also adapt the latter formula to just compare the day number of two dates 366 days apart, if they are the same there's a leap year in between, i.e. `=IF(DAY(A1-1)=DAY(A1+365),"Leap Year","No Leap Year")` Jun 11, 2015 at 12:18
• Thank you all for this formula. That's the formula I want to use in my work. Thanks Again. Jun 11, 2015 at 13:05
• If you are happy with the formula, please accept it so people can see it is solved. Jun 11, 2015 at 17:21

Some people use:

IF( date(year(A1), 2, 29) <> date(year(A1), 3, 1) ,"Leap", "No leap")

But I don't like using invalid dates in my formula, so I use this one:

IF( month( date(year(A1), 2, 28)+1 )=2 ,"Leap", "No leap")

Or you can count the number of days in the year (or in February) like so:

IF( date(year(A1) + 1, 1, 1) - date(year(A1), 1, 1) = 366 ,"Leap", "No leap")

Here is a way to calculate the days between two dates: https://support.office.com/en-ca/article/Calculate-the-difference-between-two-dates-8235e7c9-b430-44ca-9425-46100a162f38

Calculate between 1st January and 31st December. If it equals 366, then it's a leap year.

Hope that helps

Leap years happen every 4 years, on the fourth year.

Therefore the simplest solution is:

`=IF(MOD(YEAR(A1),4)=0,"Leap Year","No Leap Year")`

Identify the year, divide it by four (`MOD` function), and if remainder is zero then it's a leap year.

EDIT:

Sorry, for your specific problem I see you need it to return your chosen value during the first two months of a leap year, or else during the last ten months of the year before a leap year... Therefore you would need the formula to be:

``````=IF(MONTH(A1)<3,
IF(MOD(YEAR(A1),4)=0,"Leap Year","No Leap Year"),
IF(MOD(YEAR(A1),4)=3,"Leap Year","No Leap Year"))
``````
• Strictly speaking leap years won't always occur every 4 years, 2100 is not a leap year Jun 11, 2015 at 12:20
• If I live for another eighty-five years, I'll make sure to come back and update the formula, @barryhoudini... otherwise it's all yours! Jun 11, 2015 at 13:37
• As a response to "this statement is not true" I don't find "but it's mostly true!" all that compelling Feb 15, 2019 at 1:23
• How about 'it will be true every year of our lives'? If it will practically give you the correct result for 100% of the use cases you require it for, it's a correct answer. Pragmatism over fixing non-problems. Apr 9, 2019 at 20:11