Counting Mondays

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Counting Mondays

  1. #1
    Board Regular Yevette's Avatar
    Join Date
    Mar 2003
    Location
    Los Angeles, CA
    Posts
    336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Counting Mondays

     
    Hello All,

    I know there are several postings out there on this subject, but I can't seem to make the formula work.

    I have a start date of 9/15/03 (A2) and an end date of 10/10/03 (B2). I need a formula that will calculate the total number of Mondays in this period (not "between" the start and end dates of this period). Can someone help me with a formula to resolve this? The formula I'm using now is:

    =INT((($B$2-$A$2)-MOD(2-$B$2,7))/7)

    but it's coming up one Monday short. Can someone help me? Thanks a lot!
    F.O.C.U.S.: Follow One Course Until Successful

  2. #2
    MrExcel MVP
    Moderator
    SydneyGeek's Avatar
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    12,255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Counting Mondays

    INT usually rounds down. Why not just add 1 to the result?

  3. #3
    Board Regular Chitosunday's Avatar
    Join Date
    Jul 2003
    Posts
    1,017
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Counting Mondays

    =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A2&":"&B2)))=2))

    or another formula
    =INT((+B2-A2)/7)+IF(WEEKDAY(A2)+MOD(B2-A2,7)>=2,1,0)
    Mr Young at Heart - Learning is like chasing the wind, it's boundless.

  4. #4
    Board Regular Yevette's Avatar
    Join Date
    Mar 2003
    Location
    Los Angeles, CA
    Posts
    336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Counting Mondays

    Hi Chito!

    Worked like a charm! I thought I already tried this formula from an earlier post (I think it was your post actually), but couldn't get the formula to work. I probably transposed a cell address or something. But thanks again. Have a great day!
    F.O.C.U.S.: Follow One Course Until Successful

  5. #5
    Board Regular
    Join Date
    Jan 2003
    Location
    Round Rock, Texas
    Posts
    564
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Counting Mondays

    Another non-volatile possibility:

    =INT((B2-2)/7)-INT((A2-2)/7)+INT(WEEKDAY(A2-2)/7)

    The following is not reliable in its current form:
    =INT((+B2-A2)/7)+IF(WEEKDAY(A2)+MOD(B2-A2,7)>=2,1,0)
    For example, if A2 = 8/01/03 and B2 = 8/31/03, then it returns 5, instead of 4, and

    =SUMPRODUCT(INT((+B2-A2)/7)+IF(WEEKDAY(A2)+MOD(B2-A2,7)>={1;2;3;4;5;6;7},1,0))

    returns 35, instead of 31.

    --Tom

  6. #6
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Counting Mondays

    Hi Yevette:

    another one I played with ...

    =INT((B1-A1)/7)+OR(WEEKDAY(A1)=2,WEEKDAY(B1)=2)
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  7. #7
    Board Regular
    Join Date
    Jan 2003
    Location
    Round Rock, Texas
    Posts
    564
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Counting Mondays

    I don't like being a naysayer, but:
    =INT((B1-A1)/7)+OR(WEEKDAY(A1)=2,WEEKDAY(B1)=2)
    is not reliable either. For examples, try 8/16/03 & 8/27/03, and 9/7/03 & 9/16/03 in cells A1 and B1, respectively.

    --Tom

  8. #8
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Counting Mondays

      
    Quote Originally Posted by rrdonutz
    I don't like being a naysayer, but:
    =INT((B1-A1)/7)+OR(WEEKDAY(A1)=2,WEEKDAY(B1)=2)
    is not reliable either. For examples, try 8/16/03 & 8/27/03, and 9/7/03 & 9/16/03 in cells A1 and B1, respectively.
    --Tom
    Hi Tom:

    Thanks for pointing that out. I have corrected the formula now to read ...

    =INT((B1-A1)/7)+OR(WEEKDAY(A1)<=2,WEEKDAY(B1)>=2)

    as illustrated in ...

    ******** ******************** ************************************************************************>
    Microsoft Excel - y030912h1.xls___Running: xl97 : OS = Windows 98
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    1
    08/16/200308/27/20032
    2
    09/07/200309/16/20032
    Sheet2a

    [HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

User Tag List

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