Count number of cells between two dynamic dates

  • Thread starter Thread starter Robb
  • Start date Start date
R

Robb

I cannot figure out how to calculate the date for
the "next friday" from the current date.

specifically, I have a spreadsheet where I want to count
the number of cells that are between two dynamic dates
(the Monday before and the Friday after the current date)
within a range. Obviously the current date will change,
and the values reported will only change every seven days.

I hope that makes sense.

thanks!
 
Hi:

If A1 contains the date, the Friday immediately after that date will be:

=A1+MOD(5-WEEKDAY(A1),7)+1

I'm sure there's a a simpler way.
 
Robb,

You can get the date of the next Friday with

=TODAY()-WEEKDAY(TODAY()+1)+7

the Monday before is

=TODAY()-WEEKDAY(TODAY()+1)+3

And to count the dates in A1:A100 to be between these, use

=SUMPRODUCT((A1:A100>=TODAY()-WEEKDAY(TODAY()+1)+3)*(A1:A100<=TODAY()-WEEKDA
Y(TODAY()+1)+7))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi Bob:

Strictly speaking, if today is a Friday, your formula does not give the
*next* Friday as requested by the OP. However, it probably is what the OP
wants, reading between the lines <g>.

Regards,

Vasant.
 
Vasant,

Of course you are right, and that was deliberate on my part, as he was
asking to count dates between a Monday and a Friday and if I went for the
following week on a Friday it would be counting over 2 weeks not 1.I doubt
when someone asks for the date of the next whatever that they consider the
finer implications as you and I have, so we either ask the question, or make
an assumption, So I made an assumption<g>.

Bob
 
Thanks so much guys. I truly appreciate the help.



Robb

--
This email may contain privileged and confidential information intended only
for the use of the individual or party named above. If the reader of this
message is not the intended recipient, or the employee or agent responsible
to deliver it to the intended recipient, you are hereby notified that any
discrimination, distribution, or copying of this communication is
prohibited. If you have received this communication in error, please notify
us immediately.
 
okay, it worked. Excellent.

Now, please please please explain it. I have looked at it all morning and
can't quite figure out HOW it works. :)


thanks!


Robb

--

This email may contain privileged and confidential information intended only
for the use of the individual or party named above. If the reader of this
message is not the intended recipient, or the employee or agent responsible
to deliver it to the intended recipient, you are hereby notified that any
discrimination, distribution, or copying of this communication is
prohibited. If you have received this communication in error, please notify
us immediately.
 
Robb,

I assume that it is the SUMPRODUCT that is perplexing you.

=SUMPRODUCT((A1:A100>=TODAY()-WEEKDAY(TODAY()+1)+3)*(A1:A100<=TODAY()-WEEKDA
Y(TODAY()+1)+7))

What this is doing is evaluating all cells in A1:A100 to be GTE Last Monday,
and returning an array of TRUE or FALSE for each cell. It also evaluates
those same cells to be LTE next Friday, returning a similar array.
SUMPRODUCT than multiplies each element of array1 by each corresponding
element of array2 and adds them up. When you multiply TRUE/FALSE in this
way, you get

FALSE*FALSE=0
FALSE*TRUE=0
TRUE*FALSE=0
TRUE*TRUE=1.

So when it meets both conditions it returns 1, else it returns 0. SUMPRODUCT
adds the results up, giving you your count.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top