Results 1 to 7 of 7

Count of Weeks

This is a discussion on Count of Weeks within the Excel Questions forums, part of the Question Forums category; Can anyone advise how I could make a cell display the number of Mondays that have occurred during a specified ...

  1. #1
    New Member
    Join Date
    Oct 2002
    Location
    Melbourne
    Posts
    15

    Default

    Can anyone advise how I could make a cell display the number of Mondays that have occurred during a specified date period ? I specifically want my worksheet to display a count of weeks (Mondays) between (and including) 01-Jul-2002 and (including) the current date. Thanks.

  2. #2
    MrExcel MVP
    Join Date
    May 2002
    Posts
    13,993

    Default

    try this:

    =INT((TODAY()-"01/07/2002")/7)+IF(WEEKDAY("01/07/2002"+3)+MOD(TODAY()-"01/07/2002",7)>=7,1)


    paddy

  3. #3
    New Member
    Join Date
    Oct 2002
    Location
    Melbourne
    Posts
    15

    Default

    Thanks PADDYD. That did the trick.

  4. #4
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default

    Hi dhayes1963:

    Try the following ...

    '=INT((B2-A2)/7)+OR(AND(WEEKDAY(A2)<=C2,WEEKDAY(B2)>=C2),AND(WEEKDAY(B2)>=C2,WEEKDAY(A2)<=C2))*1


    where A2 houses the starting date, B2 houses the ending date, and C2 houses the weekday number -- in your case 2 for Monday

    Regards!
    Yogi Anand

  5. #5

    Join Date
    Oct 2002
    Posts
    49

    Default

    Since it is known that 1 Jul 2002 is a monday, the formula could be reduced to :-

    =INT((TODAY()-"1/7/2002")/7)+1

    This counts the number of Mondays.

    However, if you just want the number of complete weeks starting from 1 Jul 2002 :-

    =INT((TODAY()-"30/6/2002")/7)



    [ This Message was edited by: Pear on 2002-11-02 09:40 ]

  6. #6
    New Member
    Join Date
    Oct 2008
    Posts
    1

    Default Re: Count of # of Partial Weeks

    I would like to count the number of weeks...including partial weeks? Does such a formula exist?

  7. #7
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    19,946

    Default Re: Count of Weeks

    Hello mneff,

    Welcome to MrExcel

    You'd be more likely to get some helpful answers by starting a new thread (some users disregard threads with several answers), and you might want to give a few more details of what you want to do.

    I'll have a guess....

    If you have two dates, one in A2 and a later one in B2 then to find the number of weeks between them, rounded up so that a partial week is counted as a full one

    =INT((B2-A2+6)/7)

    or if you want to get a result like "4 weeks 3 days" try

    =INT((B2-A2)/7)&" days "&MOD(B2-A2,7)&" days"

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com