PC Review


Reply
Thread Tools Rate Thread

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

 
 
=?Utf-8?B?Um9zc3Rh?=
Guest
Posts: n/a
 
      26th May 2006
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?
 
Reply With Quote
 
 
 
 
Guest
Posts: n/a
 
      26th May 2006
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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?



 
Reply With Quote
 
 
 
 
Bob Phillips
Guest
Posts: n/a
 
      26th May 2006
=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

(replace somewhere in email address with googlemail if mailing direct)

"Rossta" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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?



 
Reply With Quote
 
SteveG
Guest
Posts: n/a
 
      26th May 2006

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...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=545867

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculate number of Mondays betweeb 2 dates k.roberts@ucas.ac.uk Microsoft Excel Worksheet Functions 6 9th Sep 2008 04:33 PM
Counting number of Mondays in a month Jason Microsoft Access Queries 6 10th Apr 2008 09:41 PM
Can you count the number of Mondays in a month Box666 Microsoft Access Getting Started 7 30th Nov 2006 06:16 PM
Calculate Next Mondays Date Deano Microsoft Access Forms 3 11th Sep 2004 03:30 PM
Number of Mondays in month Michael Noblet Microsoft Access Reports 3 28th Jul 2004 10:41 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 06:16 AM.