Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • How to reference named range different sheet in Excel
  • Find closest match in Excel
  • How to use Excel FORMULATEXT function
  • Offset in Excel
  • How to get last row in numeric data in Excel

Data Analysis Examples

  • Move chart to a separate worksheet in Excel
  • Conditional Formatting Color Scales Examples in Excel
  • Understanding Pivot Tables in Excel
  • How To Create Frequency Distribution in Excel
  • Error Bars in Excel

Data Validation Examples

  • Excel Data validation with conditional list
  • Excel Data validation date in specific year
  • Excel Data validation must not contain
  • Excel Data validation allow weekday only
  • Excel Data validation don’t exceed total

How to determine year is a leap year in Excel

by

If you want to test whether the year of a certain date is a leap year, you can use a formula that uses the MONTH, YEAR, and DATE functions.

Formula

=MONTH(DATE(YEAR(date),2,29))=2

Explanation

In the example shown, the formula in cell C5 is:

=MONTH(DATE(YEAR(B5),2,29))=2

How the formula works

The core of this formula is the DATE function, which will automatically adjust to month and year values that are out of range. In the formula, year is passed into date unchanged, along with 2 for month (February) and 29 for the day. In leap years, February has 29 days, so the DATE function will simply return the date February 29 of the year.

Worked Example:   Convert Excel time to Unix time in Excel

In non-leap years however, DATE will return the date March 1 of the year, because there is no 29th day and DATE simply rolls the date forward into the next month.

Worked Example:   Get month from date in Excel

Finally, MONTH simply extracts the month from the result provided by DATE, which is compared to 2 using the equal sign.

If month is 2, the formula returns TRUE. If not, the month must be 3 and the formula returns FALSE.

Just want to test a year?

If you just want to check if a year (i.e. 2015, 2016, etc.), modify the formula as follows:

=MONTH(DATE(year,2,29))=2

A more literal solution

If the formula above seems to “clever” and you want a more literal solution, you can test if a year contains 366 days instead with this formula:

=DATE(YEAR(date)+1,1,1)-DATE(YEAR(date),1,1)

This formula generates two dates, then subtracts January 1st of the given year from January 1st of the next year.

Worked Example:   Check If Two Dates are same month in Excel

Post navigation

Previous Post:

Series of dates by day

Next Post:

How to calculate working days left in month in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • EXACT function: Description, Usage, Syntax, Examples and Explanation
  • How to extract nth word from text string in excel
  • SEARCH, SEARCHB functions: Description, Usage, Syntax, Examples and Explanation
  • Excel MAX, MATCH and ADDRESS function illustration
  • MID, MIDB functions: Description, Usage, Syntax, Examples and Explanation
  • ISOWEEKNUM function: Description, Usage, Syntax, Examples and Explanation
  • Add workdays to date custom weekends in Excel
  • Get last weekday in month in Excel
  • Convert time to time zone in Excel
  • Get month name from date in Excel
  • YIELD function: Description, Usage, Syntax, Examples and Explanation
  • TBILLEQ function: Description, Usage, Syntax, Examples and Explanation
  • Calculate payment for a loan in Excel
  • PPMT function: Description, Usage, Syntax, Examples and Explanation
  • FVSCHEDULE function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2022 xlsoffice . All Right Reserved. | Teal Smiles