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
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 communityI 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
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.
=IF(DAY(A1-1)=DAY(A1+365),"Leap Year","No Leap Year")
Jun 11, 2015 at 12:18
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"))