Forum Discussion

tikitour's avatar
tikitour
Copper Contributor
Apr 20, 2023

Counting days between two dates, but ignore leap year

I have a spreadsheet that has a start and end date. It's easy enough to work out the difference "in days" between these two dates, however when the dates fall within a leap year it adds the extra day...
excel
Formulas and Functions
NikolinoDE's avatar
NikolinoDE
Platinum Contributor
Apr 20, 2023

tikitour 

You can use a formula in Excel to calculate the number of days between two dates while ignoring leap years.

Here’s an example of how you could do this:

=DATEDIF(A2,B2,"d")-IF(AND(MONTH(A2)<=2,MONTH(B2)>=3,YEAR(A2)=YEAR(B2)),1,0)

In this example, A2 and B2 are the cells that contain the start and end dates, respectively. The DATEDIF function calculates the number of days between the two dates. The IF function checks if the start date is before or on February and the end date is on or after March of the same year (i.e., the date range includes February 29th of a leap year). If this condition is true, the formula subtracts 1 from the result of the DATEDIF function to ignore the extra day added by the leap year.

You can adjust this formula to fit your specific needs by changing the cell references and adding additional conditions as needed.

I hope this helps! 

Resources