Counting # of days between 2 dates excluding Fri & Sat)

[Bernd P]

Hello Jarek,

The formula looks fine, maye you got an input date wrong - perhaps got
tricked by Excel "auto century guess" 1900 resp. 2000?

But if you want to count single weekdays there is a better non-
volatile formula:
http://www.sulprobil.com/html/date_formulas.html

Regards,
Bernd

 

[Bernd P]

Hello,

Now let's have a look how long our formulas take to compute. I ran
them on 5 pairs of dates, 1 day, 1 week, 1 month, 1 year and 10k days
difference and got from FASTEXCEL:
Biff''s SUMPRODUCT 10.61 ms
Mike' NETWORKDAYS 0.23 ms
Biff''s SUM 0.15 ms
Bernd's INT/MOD 0.11 ms

Regards,
Bernd

 

[T. Valko]

<rant>

That's all fine and good.

Here's one thing that I think is important (at least it is to me), I
understand and can explain how the SUMPRODUCT formula works. If I need/want
to change it for other conditions I can do it easily.

Do you really understand these formulas:

=B1-A1+1-INT((B1-MOD(B1-6,7)-A1+7)/7)-INT((B1-MOD(B1-7,7)-A1+7)/7)
=SUM(INT((WEEKDAY(A1-{1,2,3,4,5})+B1-A1)/7))

I don't!

I mean, yeah sure, I know what INT, MOD, SUM and WEEKDAY do. I know what
"plus this minus that divided by this" means. But, can you explain the
*logic of why* the formulas do "plus this minus that divided by this"? I
can't! If I needed/wanted to change those other formulas for other
conditions I don't think It'd be that easy and would take some time to
experiement.

I have the SUM(INT formula in my "library" but I don't suggest it because I
can't explain how it works if someone asks.

I could respond by saying:

You subtract this from that then add this then subtract that and divide by
this but what kind of an explanation is that? That's the explanation of
someone that doesn't understand what they're talking about!

So, my challenge to you is: explain how those formulas work!

</rant>
<VBG>

 

[Bernd P]

Hello Biff,

Of course I can - my INT/MOD one I derived on my own - but Daniel M.
was the first one to show it (with the weekday function), I presume:
http://www.sulprobil.com/html/date_formulas.html

My MOD() part just calculates the weekday which is used to shift the
calculation to the right edge of the /7 cut, and the INT / 7 part cuts
the right weeks.

OT: If you need more details, come to London or to Berlin for a
beer

Regards,
Bernd

 

[Jarek Kujawa]

it looks fine to me too
but despite everything is set OK with the dates in my worksheet the
result still comes wrong
thks for yr response

 

[T. Valko]

What dates do you have in A1 and A2?

 

[Jarek Kujawa]

2008-10-01 and 2009-09-30
I've tried almost all Date formats provided by MS, also General and
Number
no avail

 

[David Biddulph]

I get 52 (with Excel 2003).
Have you double-checked what =A1 and =A2 show you in General format. Should
be 39722 and 40086 respectively.
It may also be worth copying the formula from the formula bar and pasting it
in here just in case you've got a hiccup in the formula.

 

[T. Valko]

Using Excel 2007 with regional date settings of U.S. English m/d/yyyy...

A1 = 10/1/2008
A2 = 9/30/2009

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)))=5))

Returns 52 which is correct.

 

[Jarek Kujawa]

thanks T.
I checked it on Excel 2007 and 2003, on 2 different PCs
to na avail
I think there might be some bug in WEEKDAY (or I don't know where)
function as both my versions of Excel are Polish (mistake in
translation or sth.)

Using Excel 2007 with regional date settings of U.S. English m/d/yyyy...

A1 = 10/1/2008
A2 = 9/30/2009

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)))=5))

Returns 52 which is correct.

--
Biff
Microsoft Excel MVP

"David Biddulph" <groups [at] biddulph.org.uk> wrote in message

I get 52 (with Excel 2003).
Have you double-checked what =A1 and =A2 show you in General format..
Should be 39722 and 40086 respectively.
It may also be worth copying the formula from the formula bar and pasting
it in here just in case you've got a hiccup in the formula.

- Pokaż cytowany tekst -

 

[Jarek Kujawa]

thks David
both values are same as qouted by you

 

[T. Valko]

So, I can't get an explanation unless I come to London or Berlin for a beer?

I don't know...

By the time I "get it" we'll have drank the towns dry!

 

[David Biddulph]

In which case, why not try putting the dates from A1 to A2 in a range of
cells and see what the WEEKDAY function produces from each date?

Even then, it wouldn't make sense to get 2555 as you are applying SUMPRODUCT
to a range of Booleans over a range of only 365 rows, so it can't return
2555.
I suggest that you break down your formula a stage at a time to debug it.
It's a simple enough formula, so the error can't be a complicated one.
Start by looking at what =(A1&":"&A2) returns. It should be 39722:40086
You could also try =COUNT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2))))) as an array
formula, which should return 365.

I did say in another message:
"It may also be worth copying the formula from the formula bar and pasting
it in here just in case you've got a hiccup in the formula."
If you do that, it would help people to help you.
--
David Biddulph

thanks T.
I checked it on Excel 2007 and 2003, on 2 different PCs
to na avail
I think there might be some bug in WEEKDAY (or I don't know where)
function as both my versions of Excel are Polish (mistake in
translation or sth.)

Using Excel 2007 with regional date settings of U.S. English
m/d/yyyy...

A1 = 10/1/2008
A2 = 9/30/2009

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)))=5))

Returns 52 which is correct.

--
Biff
Microsoft Excel MVP

"David Biddulph" <groups [at] biddulph.org.uk> wrote in
message

I get 52 (with Excel 2003).
Have you double-checked what =A1 and =A2 show you in General format.
Should be 39722 and 40086 respectively.
It may also be worth copying the formula from the formula bar and
pasting it in here just in case you've got a hiccup in the formula.

Jarek Kujawa wrote:
2008-10-01 and 2009-09-30
I've tried almost all Date formats provided by MS, also General and
Number
no avail

What dates do you have in A1 and A2?

it looks fine to me too
but despite everything is set OK with the dates in my worksheet
the result still comes wrong
thks for yr response

Hello Jarek,

The formula looks fine, maye you got an input date wrong -
perhaps got tricked by Excel "auto century guess" 1900 resp.
2000?

But if you want to count single weekdays there is a better non-
volatile
formula:http://www.sulprobil.com/html/date_formulas.html

Regards,
Bernd- Ukryj cytowany tekst -

- Poka¿ cytowany tekst -- Ukryj cytowany tekst -

- Pokaz cytowany tekst -

 

[Jacob Skaria]

Range("A1") = startDate
Range("B1") = EndDate
Range("C1") =
(B1-WEEKDAY(B1,1)+WEEKDAY(A1,1)-A1)/7*5-MIN(5,WEEKDAY(A1,1))+MIN(5,WEEKDAY(B1,1))

If this post helps click Yes