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. Guest

    Guest Guest

    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
    #1
    1. Advertisements

  2. Guest

    Guest Guest

    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
    #2
    1. Advertisements

  3. Guest

    Bob Phillips Guest

    =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" <> 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
    #3
  4. Guest

    SteveG Guest

    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
    View this thread: http://www.excelforum.com/showthread.php?threadid=545867
     
    SteveG, May 26, 2006
    #4
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest

    calculating number of days (e.g., Mondays) between two dates

    Guest, Jul 13, 2005, in forum: Microsoft Excel Worksheet Functions
    Replies:
    10
    Views:
    283
  2. Guest

    Finding people born in a given month or after a given year

    Guest, Sep 19, 2005, in forum: Microsoft Excel Worksheet Functions
    Replies:
    1
    Views:
    318
    Ron de Bruin
    Sep 19, 2005
  3. harnagel

    Count Mondays worked in Month

    harnagel, Dec 5, 2005, in forum: Microsoft Excel Worksheet Functions
    Replies:
    6
    Views:
    220
    Bob Phillips
    Dec 6, 2005
  4. harnagel

    Count Mondays worked in Month

    harnagel, Dec 8, 2005, in forum: Microsoft Excel Worksheet Functions
    Replies:
    2
    Views:
    323
    SteveG
    Dec 8, 2005
  5. Calculate number of Mondays betweeb 2 dates

    , Sep 8, 2008, in forum: Microsoft Excel Worksheet Functions
    Replies:
    6
    Views:
    187
    Roger Govier
    Sep 9, 2008
Loading...

Share This Page