+ Reply to Thread
Results 1 to 3 of 3

Date Differences and ignoring Leap Years

  1. #1
    Registered User
    Join Date
    09-20-2010
    Location
    dublin
    MS-Off Ver
    Excel 2003
    Posts
    2

    Date Differences and ignoring Leap Years

    Hi,

    Im trying to get the difference in days between 2 dates ignoring the leap years.

    I have searched google and checked lots of formulas. The closest I have got is this -

    = B1 - DATE(YEAR(B1), MONTH(A1), DAY(A1)) + 365 * (YEAR(B1) - YEAR(A1)) - SIGN(B1 - A1) * (DATE(YEAR(B1), 3, 1) - DATE(YEAR(B1), 2, 29))

    However on closer checking. Its returning a total of 364 days where i use the 2 dates 19/01/2007 (A1) and 19/01/2008 (B1). I would be expecting a total of 365 days.

    Can anyone help me out on this??

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Date Differences and ignoring Leap Years

    To count all days except leap days

    =SUMPRODUCT((TEXT(ROW(INDIRECT(A1&":"&B1)),"ddmmm")<>"29Feb")+0)-1
    Last edited by daddylonglegs; 09-20-2010 at 11:50 AM.
    Audere est facere

  3. #3
    Registered User
    Join Date
    09-20-2010
    Location
    dublin
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Date Differences and ignoring Leap Years

    that seems to it...thanks a million!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1