Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to count number of leap years between two dates in Excel?

In Excel, to count the number of years between two given dates may be easy for most of users, but, can you only count the number of leap years also called intercalary year between two dates? Here I introduce a formula can help you to quickly calculate the number of leap years between a date range in Excel.

Count leap years in a date range with formula


Add Years/Months/Days to Date

To quickly add a specific number of days to a given date, Kutools for Excel's Add Years/Months/Days to Date utilities can give you a favor.
doc add year month day

arrow blue right bubble Count leap years in a date range with formula


To count leap years between two dates, you just need to do as this:

Select a blank cell that you will place the counted result at, C2 for instance, and enter this formula
=DATE(YEAR(B2),1,1)-DATE(YEAR(A2),1,1)-((YEAR(B2)-YEAR(A2))*365)+AND(MONTH(DATE(YEAR(A2),2,29))=2,MONTH(DATE(YEAR(B2),2,29))=2)*1
then press Enter key to get the result. See screenshot:
doc count leap year 1

Tip: In the formula, A2 is the start date and B2 is the end date, you can change them as you need.

doc download 1

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Fabian Ries · 8 months ago
    Oi amigo,

    Excelente fórmula!

    Trabalhei com ela e percebi que há uma divergência quando o primeiro e/ou o segundo intervalo são anos bissextos. Tomei a liberdade de corrigir (em português):


    =DATA(ANO(B2);1;1)-DATA(ANO(A2);1;1)-((ANO(B2)-ANO(A2))*365)+E(ANO(A2)=ANO(B2);MÊS(DATA(ANO(A2);2;29))=2;A2<=DATA(ANO(A2);2;29);B2>=DATA(ANO(B2);2;29))+E(ANO(A2)