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".
Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.Sign up to join this community
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.
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.
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"))