Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calc days between two dates and exclude leap year days scoz Excel Worksheet Functions 5 November 23rd 07 04:58 PM
How to determine if year is a leap year Wanda Excel Worksheet Functions 7 September 17th 07 07:48 AM
Number of days in a year XtraNeed Excel Discussion (Misc queries) 4 September 21st 05 03:37 PM
Calculating days between dates and leap years KimberlyC Excel Programming 18 March 17th 05 05:02 AM
Calculating days between dates and leap years KimberlyC Excel Programming 1 March 12th 05 05:23 AM


All times are GMT +1. The time now is 09:39 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"