# Thread: Odd lapsed days algorithm needed.

1. ## 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. 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. 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

4. 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).

5. 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. The start date is never included in the calculation. I've managed to handle it, however.

7. 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?

8. 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
•