Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Number of leap year days between two dates
I realize I'm necro'ing an old old thread... but it shows up in google searches and people should benefit from the solution I came up with...
=DATE(YEAR(EndDate),1,1)-DATE(YEAR(StartDate),1,1)-((YEAR(EndDate)-YEAR(StartDate))*365)+AND(MONTH(DATE(YEAR(EndDate) ,2,29))=2,DATE(YEAR(EndDate),2,29)<EndDate)-AND(MONTH(DATE(YEAR(StartDate),2,29))=2,DATE(YEAR( StartDate),2,29)<StartDate) Basically it finds full year days(Jan 1 StartYear to Jan 1 StartYear), then it figures out how many days above 365 per year the net of EndDate - StartDate(because Excel includes leap days), then it adjusts that number based on if the start or end dates are in leap years as well as the startDate being before Feb 29th(meaning the date range experienced that leap day) or the endDate is after Feb 29th(again maining the date range experienced the leap day). If you are questioning why we minus when the start date is past Feb 29th, it is because when we normalize for a full year(very first part), we ASSUME the date range experienced the leap day... so if it didn't... we need to subtract 1 day. Hopefully this helps somebody :P took me a few hours to figure it out... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calc days between two dates and exclude leap year days | Excel Worksheet Functions | |||
How to determine if year is a leap year | Excel Worksheet Functions | |||
Number of days in a year | Excel Discussion (Misc queries) | |||
Calculating days between dates and leap years | Excel Programming | |||
Calculating days between dates and leap years | Excel Programming |