Figuring number of Mon. Tues. Wed between dates

  • Thread starter Thread starter Paula
  • Start date Start date
P

Paula

Course begins January 1, 2008
Course ends May 30, 2008
How many Tuesdays are there. Of course the dates will change.
Thanks
 
=SUM(IF(WEEKDAY(ROW(INDIRECT("$1:$"&enddate-startdate+1))+$F$2-1)=3,1,0))
Change the =3 to 2 for Mondays and 4 for Wednesdays.

This is an **array** formula. After typing the formula, instead of pressing
just ENTER, press CTRL+SHIFT+ENTER.
 
One way...

A1 = start date
B1 = end date

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

Where =2 means the 2nd day of the week = Tuesday
 
I've been on an array formula kick, but I like your formula, but you might
wanna change the A151 in the INDIRECT statement to B1 :)
 
Typo...
A1 = start date
B1 = end date
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A151)),2)=2))
Click to expand...

Formula should be:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)=2))
 
Hi,

Here is another formula:

=SUMPRODUCT(--(MOD(ROW(INDIRECT(G23&":"&G24)),7)=3))
 
Here's a much more compact formula that does the same thing:

=INT((WEEKDAY(A1-n,2)+B1-A1)/7)

Where n = day of week: Monday =1 thru Sunday =7

I don't ever suggest it because I don't understand the logic behind it (and
no one has been able to explain it to me!) and if someone asks for an
explanation I won't have one! I don't post anything I can't explain.
 
Thanks so much. I have tried both formulas and I am not doing something
correct. I have the beginning date in A1 and the end date in B1. I am not
seeing where B1 is referenced. I changed A151 to B1 but it did not work. I
actually do not understand the formula such as -- but of course that is OK if
I can just get it to work. I am getting a REF error. This is the formula in
A4. I have copied it =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A151)),2)=2))
Sorry for the confusion.
 
Back
Top