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

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

--
Biff
Microsoft Excel MVP


"Bernd P" <bplumhoff@xxxxxxxxx> wrote in message
news:17ea5efb-a3ef-4b34-a83e-65459203488b@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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


.

Relevant Pages

  • Re: Normalize (or normalise depending on US/UK...)
    ... One way to set the min. to 0 and the max. to 1 would be to subtract ... the min. from each number and then divide the result by the new max. ... sets (long explanation of why). ... Microsoft MVP Excel 2000-present ...
    (microsoft.public.excel.charting)
  • Re: subtract function
    ... There are functions for SUM and PRODUCT, where the order of the arguments ... There is no SUBTRACT function; there's also no DIVIDE ... You accomplish operations like this with with "operators", ...
    (microsoft.public.excel.misc)
  • Re: IF/SUMIF function
    ... column B and divide the sum of C by the sum of B, ... subtract 1. ... It has to look at all the data so that when we do populate ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Need help creating a formula
    ... > of column K, then subtract the sum of column K from the sum of column E, then ... > divide this # by the sum of column E. I've tried a variety of combinations ...
    (microsoft.public.excel.newusers)
  • Re: Putnam Exam 2004 -- [*SPOILERS*]
    ... the area and side lengths of a triangle satisfy ... > the first column of the matrix in the determinant is a multiple of u_n, ... > we can then divide through to make the constant be 1. ... > can divide X by c to get a sum which is a multiple of x1, ...
    (sci.math)