Results 1 to 8 of 8

Thread: Find Number Of Mondays In given Period
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2005
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Find Number Of Mondays In given Period

    hi everybody
    find Number Of Mondays In Period

    I need the number of Monday's that occur within an interval between two dates,
    I use the following Array Formula:

    =SUM(IF(WEEKDAY(A2-1+ROW(INDIRECT("1:"&TRUNC(B2-A2)+1)))=C2,1,0))

    This formula assumes the following:
    A2 contains the beginning date of the interval
    B2 contains the ending date of the interval
    C2 contains the day-of-week number (1=Sunday)

    but same one is not working in my spreadsheet.

    can anybody guide me.?

  2. #2
    Board Regular
    Join Date
    Sep 2002
    Location
    Kettering , Northants
    Posts
    623
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Kamlakar,

    Would pushing your data through a pivot table give
    you the results you reqiure ???.

    So if 2 = Monday , use "count of 2" within your table.

    Hope this helps

    Russ.

  3. #3
    New Member
    Join Date
    Jun 2005
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Find Number Of Mondays In given Period

    hi !

    this is not pivot table data

    even if i changed formula as per your suggestion
    it wont worked.

    please help me !

    kamlakar

  4. #4
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    3 Thread(s)

    Default

    What you posted is an array formula. After typing it you must press Ctrl+Shift+Enter, not just Enter. If correctly entered Excel will surround it with curly braces {}.

  5. #5
    MrExcel MVP Richie(UK)'s Avatar
    Join Date
    May 2002
    Location
    UK
    Posts
    3,329
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi k,

    Have you entered the array formula with the required CSE keystrokes?

    CSE = Control+Shift+Enter

    Chip's formula works OK when entered in that way.
    Richie

  6. #6
    Board Regular
    Join Date
    Oct 2002
    Location
    Harefield, Middlesex, England
    Posts
    241
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Maybe I'm missing somthing here???

    But I don't think you need an array formula...

    I'm also not clear about what is in C2, i.e. why it's relevant to the problem if it's always Mondays you're interested in?

    Do you want to include the start and end dates, or just the dates between?

    I haven't fully formulated this yet, but what I'm thinking is along the lines of;

    =INT((B2-A2/)7) plus you might need to add 1 depending on the start day and the size of the remainder...

    There's always a Monday in every 7 day period right?

    Unless, as I say, I'm missing something??

    Regards,

    Simsy

  7. #7
    MrExcel MVP fairwinds's Avatar
    Join Date
    May 2003
    Posts
    8,638
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    Try:


    =INT((B2-A2+WEEKDAY(A2-2))/7)
    "Fair Winds and Following Seas"

  8. #8
    New Member
    Join Date
    Jun 2005
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Find Number Of Mondays In given Period

    Thanks everybody

    u have help me to solve my problem

    really mr excel is great.

    lot of thanks again !

    kamlakar

Some videos you may like

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
  •