# How do I calculate number of Mondays in a given month in Excel?

Discussion in 'Microsoft Excel Worksheet Functions' started by Guest, May 26, 2006.

1. ### GuestGuest

I'm trying to get Excel to calculate the number of Mondays, Tuesdays, etc in
a given month but I haven't found a function that addresses this task. Any
ideas?

Guest, May 26, 2006

2. ### GuestGuest

Hi

Have a look here:
http://www.cpearson.com/excel/DateTimeWS.htm
It's on that page - somewhere - along with tons of other useful stuff!

Andy.

"Rossta" <> wrote in message
news:...
> I'm trying to get Excel to calculate the number of Mondays, Tuesdays, etc
> in
> a given month but I haven't found a function that addresses this task.
> Any
> ideas?

Guest, May 26, 2006

3. ### Bob PhillipsGuest

=4+(DAY(A1-DAY(A1)+35)<WEEKDAY(A1-DAY(A1)-DoW))

where DoW is the day number to test, 1 Sun, 2 Mon

--
HTH

Bob Phillips

"Rossta" <> wrote in message
news:...
> I'm trying to get Excel to calculate the number of Mondays, Tuesdays, etc

in
> a given month but I haven't found a function that addresses this task.

Any
> ideas?

Bob Phillips, May 26, 2006
4. ### SteveGGuest

You could use this with a helper column,

=SUMPRODUCT(--(WEEKDAY(DATE(2006,1,K1:K31),1)=2))

Where K1:K31 contains the numbers 1 - 31 and "=2" is the day of the
week you are looking for (1=Sun, 2=Mon...)

If you don't want to use a helper column just type out the numbers in
the formula where the K1:K31 range is like {1,2,3,4,,,,,,31}.

HTH

Steve

--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=7571