Extracting a # of days from a date range

  • Thread starter Thread starter Ivan
  • Start date Start date
I

Ivan

Hi

New to Excel, so apologies if this is a really obvious
question.....

I want to be able to put in two dates and get Excel to
tell me how many Fridays are within that range - I imagine
it is doable, but I can't figure out how. Any help
appreciated!

Thanks
Ivan
 
Start date in A1 and end date in A2

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

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
Hi Ivan,

A1: start date
A2: end date

=INT((A2-WEEKDAY(A2-5)-A1+8)/7)

Regards,

Daniel M.
 
Ivan,
....that works too. Now I just got to figure out how they
work ;)
Click to expand...

I'll try explaining them to you but remember that I'm biased :-)

Just remember that dates are stored as numbers.

' =========================
Peo's formula builds an array of N dates (N being the number of days between A1
and A2) and verifies for each one if it is a Friday (Weekday =5).
That result is an intermediary array of TRUE/FALSE.
You can change them to 1/0 by applying the -- operator.
SUMPRODUCT sums them up (you could use SUM but it would require you to enter the
formula as an ARRAY formula)
I agree the once you understand the ROW(INDIRECT()) concept to build array of
numbers, this formula seems easier to understand.


' =========================
The formula I provided works on the following principle (for any A2>=A1):

We must get the previous/current Friday to A2 (called PCFA2)
PCFA2 = A2-WEEKDAY(A2-5)+1


If you substract the number of days between PCFA2 and A1, giving nD
For nD 0 to 6 => 1 Friday
For nD 7 to 13=> 2 Fridays
etc.

So:
NFridays = INT(nD /7)+ 1
NFridays = INT(nD /7)+ 7/7
NFridays = INT((nD +7)/7) ' putting the 7 inside
NFridays = INT((PCFA2-A1+7)/7) ' resolving nD => PCFA2-A1
NFridays = INT(( A2-WEEKDAY(A2-5)+1-A1+7)/7) ' resolving PCFA2=>
A2-WEEKDAY(A2-5)+1
NFridays = INT(( A2-WEEKDAY(A2-5)-A1+8)/7) ' simplifying 1+7=8

One thing to remember, even if the dates are separated by many days, this
formula will only invoque ONE call to WEEKDAY(). But I told you I was biased
;-))

Regards,

Daniel M.
 
Back
Top