Results 1 to 8 of 8
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Odd lapsed days algorithm needed.

    I have a situation where the start date (A1, for example) and end date (B1) exist.
    The start date is never calculated in the lapsed days.

    If the start date and end date include ONE weekend, the weekend 2 days aren't counted.

    If the start date and end date include more than one weekend, the start day isn't counted, neither is the first weekend 2 days, but any other weekend days are counted.

    Any thoughts? Or an improvement over this:

    =IF((SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)>5)))=2,B2-A2-2,(B2-A2)-0.5*SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)>5)))

  2. #2
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    I would write a custom function called a UDF (user defined function) where you would only have to
    =DaysCount(a1,b1). This is macro code.

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    KW,

    Try this:
    =IF(DAYS360(A1,A2,FALSE)-NETWORKDAYS(A1,A2)+1<=2,NETWORKDAYS(A1,A2),DAYS360 (A1,A2,FALSE)-2)

    DateDifference.png

    Translation: During the interval, if the number of weekend days is <=2 then return the number of weekdays +the starting date. If the number of weekend days is >2 then return the total number of days minus the start day and the first weekend.


    HTH,
    Maud
    Last edited by Maudibe; 2014-09-22 at 21:34.

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    With 9/11/14 and 9/15/14 I get -29924 but can't see where the fix needs to be (not enough coffee yet).

    KW

    Sorry...was trying it in A1 and B1...not DOWN the A column. However, 9/11/14 - 9/15/14 overlaps 1 weekend so it shouldn't be included. The result should be 1 (just the 15th).
    Last edited by kweaver; 2014-09-23 at 11:14.

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    If the start date and end date include ONE weekend, the weekend 2 days aren't counted.
    Sorry KW, but following your guidelines, I don't know how you come up with 1 for an answer instead of 3.

    numdays.png

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    The start date is never included in the calculation. I've managed to handle it, however.

  7. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    The start date is never calculated in the lapsed days.
    How did I miss that line?

    Even though you have this solved, just as an after thought, removing the the start date would drop it down by 1, but wouldn't the Friday and Monday be counted in the sample you provided in post #4?
    Last edited by Maudibe; 2014-09-23 at 19:06.

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Yes, Maud, the Friday and Monday would be counted if the dates are 9/11/14 - 9/15/14.

Posting Permissions

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