Different ways to tell if a year is leap year, using Excel formulas

Before starting, please setup the worksheet with the following:

Cell address Formula Value Namea Purpose
B3 2012 year Year number (user input)
B4 =DATE(year,1,1) 1/1/2012 Jan1st Date of Jan 1st
B5 =DATE(year,2,1) 2/1/2012 Feb1st Date of Feb 1st
B6 =DATE(year,3,1) 3/1/2012 Mar1st Date of Mar 1st
B7 =DATE(year,4,1) 4/1/2012 Apr1st Date of Apr 1st
a = Name of the range.

Then, in any other cells, input the codes in red font as indicated in the list below:
Results will be TRUE or FALSE depending on the year inputted. In this case it will always be TRUE for year 2012.

Different ways (formula) to check if a year is leap year:

  1. The year has 366 days
    =DATE(year+1,1,1)-Jan1st=366
    =DATE(year+1,1,1)-DATE(year,1,1)=366

  2. Feb. 29th is not Mar. 1st
    =DATE(year,2,29)<>Mar1st
    =DATE(year,2,29)<>DATE(year,3,1)

  3. February has 29 days
    =DAY(EOMONTH(Feb1st,0))=29
    =DAY(EOMONTH(DATE(year,2,1),0))=29

  4. February 1st and March 1st are not on same day of week
    In non leap years, Feb has 28 days (a multiple of 7), so both Feb and March start on same day of week.
    =WEEKDAY(Feb1st)<>WEEKDAY(Mar1st)
    =WEEKDAY(DATE(year,2,1))<>WEEKDAY(DATE(year,3,1))

  5. Adding 365 to January 1st does not change year
    =YEAR(Jan1st)=YEAR(Jan1st+365)
    =YEAR(DATE(year,1,1))=YEAR(DATE(year,1,1)+365)

  6. 30th Day of February is March 1st
    =DATE(year,2,30)=Mar1st
    =DATE(year,2,30)=DATE(year,3,1)

  7. Two days between February 28th and March 1st
    =DATE(year,2,28)+2=Mar1st

frog leap year
  1. "2/29/YYYY" is a number or a day
    =ISNUMBER(DATEVALUE("2/29/"&year))
    =ISNUMBER(1*("2/29/"&year))
    =ISNUMBER(1*("29-Feb-"&year))
    =NOT(ISERR(1*("29-Feb-"&year)))
    =NOT(ISERROR(DATEVALUE("2/29/"&year)))
    =AND(ISNUMBER(1*(year&"-02-29")),(year>1900))

  2. 0th Day of March is Feb 29th
    In real world there is no zeroth day for any month. But in Excel, since all dates are numbers,
    0th day refers to last day of previous month.
    =DAY(DATE(year,3,0))=29

  3. April 1st and January 1st are on same day of week
    In leap years, there are 91 days between January 1st and April 1st.
    And since 91 is a multiple of 7, both April 1st and January 1st start on same day of week
    =WEEKDAY(Jan1st)=WEEKDAY(Apr1st)
    =WEEKDAY(DATE(year,1,1))=WEEKDAY(DATE(year,4,1))

  4. Next year's February 1st and this year's February 2nd are NOT on same day of week
    In non-leap years, there are 364 days between February 2nd and next year's February 1st.
    Since 364 is a multiple of 7, both of these days are on same day of week.
    Which is not the case in leap years (as the difference becomes 365).
    =WEEKDAY(Feb1st+1)<>WEEKDAY(EDATE(Feb1st,12))
    =WEEKDAY(DATE(year,2,1)+1)<>WEEKDAY(EDATE(DATE(year,2,1),12))

  5. February starts and ends on same day of week
    29 days means both 1st and 29 are on same weekday
    =WEEKDAY(Feb1st)=WEEKDAY(EOMONTH(Feb1st,0))
    =WEEKDAY(DATE(year,2,1))=WEEKDAY(EOMONTH(DATE(year,2,1),0))

  6. The year is divisible by 400, or if it is divisible by 4 not by 100.
    =((MOD(year,4)=0)*((MOD(year,100)<>0)+(MOD(year,400)=0))=1)

  7. if the 60th day of the year is on February
    =MONTH(DATE(year,1,60)=2
    =IF(MONTH(DATE(year,1,1)+59)=2,TRUE,FALSE)

  8. Days between March 1st and February 1st are not divisible by 7
    =MOD(Mar1st-Feb1st,7)=1