-4

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

Improve this question
6
  • 1
    Could help: Method to determine whether a year is a leap year
    – bummi
    Jun 10, 2015 at 17:51
  • 1
    @Sumeet - What have you tried?
    – Ramhound
    Jun 10, 2015 at 17:52
  • 2015 is not a leap year. 2016 is a leap year.
    – DavidPostill ♦
    Jun 10, 2015 at 17:52
  • So specifically, you don't care about dates outside that very, very narrow range?
    – ChrisInEdmonton
    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.
    – Sumeet
    Jun 10, 2015 at 18:05

4 Answers 4

Reset to default
4

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.

Improve this answer
4
  • That's a clever solution!
    – eirikdaude
    Jun 11, 2015 at 11:29
  • 1
    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")
    – barry houdini
    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.
    – Sumeet
    Jun 11, 2015 at 13:05
  • If you are happy with the formula, please accept it so people can see it is solved.
    – gtwebb
    Jun 11, 2015 at 17:21
2

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")

Improve this answer
0

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

Improve this answer
0

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"))
Improve this answer
4
  • 3
    Strictly speaking leap years won't always occur every 4 years, 2100 is not a leap year
    – barry houdini
    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!
    – Steve can help
    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
    – Alex M
    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.
    – Steve can help
    Apr 9, 2019 at 20:11

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .