number of specific weekdays
 

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

number of specific weekdays
 

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

"afdmello" wrote in message
...
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

number of specific weekdays
 

On Sun, 18 Oct 2009 17:23:14 +0300, "afdmello"
wrote:

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

number of specific weekdays
 

Hi,

Try this. C6 has 1/1/2009 and C7 has 30/9/2009

=SUMPRODUCT(1*(WEEKDAY(ROW(INDIRECT(C6&":"&C7)))={ 1,3,4}))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"afdmello" wrote in message
...
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

number of specific weekdays
 

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
"Ashish Mathur" wrote in message
...
Hi,

Try this. C6 has 1/1/2009 and C7 has 30/9/2009

=SUMPRODUCT(1*(WEEKDAY(ROW(INDIRECT(C6&":"&C7)))={ 1,3,4}))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"afdmello" wrote in message
...
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

number of specific weekdays
 

On Sun, 18 Oct 2009 15:22:12 GMT, Lars-Åke Aspelin
wrote:

On Sun, 18 Oct 2009 17:23:14 +0300, "afdmello"
wrote:

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

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

number of specific weekdays
 

You are welcome

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"afdmello" wrote in message
...
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
"Ashish Mathur" wrote in message
...
Hi,

Try this. C6 has 1/1/2009 and C7 has 30/9/2009

=SUMPRODUCT(1*(WEEKDAY(ROW(INDIRECT(C6&":"&C7)))={ 1,3,4}))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"afdmello" wrote in message
...
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