number of specific weekdays

  • Thread starter Thread starter afdmello
  • Start date Start date
A

afdmello

I undertake trainings on sundays, wednesdays and Thursdays.

Is there a way that I can get the number of sundays, tuesdays and wednesdays
from January 2009 to September 30 2009

I use excel 2007

Thanking you

Afd
 
Hi Afd,

Supposing that you have a sheet with all days from 01/01/2009 till
30/09/2009.

={SUM(--(WEEKDAY(A1:A400)=1))}

Should count all Sundays in the range. Use 3 for Tuesdays and 5 for
Thursdays.

You have to enter this function as an array function, (control-shift -enter,
do not include the brackets).

Wkr,

JP
 
I undertake trainings on sundays, wednesdays and Thursdays.

Is there a way that I can get the number of sundays, tuesdays and wednesdays
from January 2009 to September 30 2009

I use excel 2007

Thanking you

Afd

If your (inclusive) start date is in cell A1 and your (includive) end
date is in cell A2, try the following formula:

=SUMPRODUCT(LOOKUP(WEEKDAY(A$1+ROW(OFFSET(A$1,,,A$2-A$1+1))),ROW($1:$7),{1,0,1,0,1,0,0}))

The {1,0,1,0,1,0,0} at the end of the formula is a vector representing
Sunday, Monday, ..., Saturday where I have put a 1 for those weekdays
to be counted and a 0 for the others.

Hope this helps / Lars-Åke
 
WOH!!

Thanks a million.

I am amazed at your diligence and desire to help.I tried Ashish',Barry's and
Lars formulas and all returned the same answer which I am certain is right.
Different ways to do the same thing

I am deeply grateful and looking forward to your continued help.

Afd
 
If your (inclusive) start date is in cell A1 and your (includive) end
date is in cell A2, try the following formula:

=SUMPRODUCT(LOOKUP(WEEKDAY(A$1+ROW(OFFSET(A$1,,,A$2-A$1+1))),ROW($1:$7),{1,0,1,0,1,0,0}))

The {1,0,1,0,1,0,0} at the end of the formula is a vector representing
Sunday, Monday, ..., Saturday where I have put a 1 for those weekdays
to be counted and a 0 for the others.

Hope this helps / Lars-Åke

Just noticed that there is an error in the formula I proposed, changed
A$1 to A$7 in one place.

Here is a the new version:

=SUMPRODUCT(LOOKUP(WEEKDAY(A$1+ROW(OFFSET(A$7,,,A$2-A$1+1))),ROW($1:$7),{1,0,0,0,1,0,0}))

Lars-Åke
 
Back
Top