+ Reply to Thread
Results 1 to 8 of 8

Weekly Reports

  1. #1
    Registered User
    Join Date
    07-28-2009
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2003
    Posts
    7

    Weekly Reports

    Hi, in need of some help....

    i need to develop a rule in which will calculate the which week of the month it is based upon todays date.

    If anyone could help me with this that would be greatly appreciated.

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Weekly Reports

    Hi,

    can you describe the "rules" you want to apply in plain English? Take this month for example. 3-Aug is a Monday. Is that week 1 or week 2?

  3. #3
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Weekly Reports

    in plain English
    thats rich considering your respective locations
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  4. #4
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Weekly Reports

    LOL, you're right, the Ozzies can't do plain English, and I have a German accent.


  5. #5
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Weekly Reports

    Batman, please clarify, when does week 2 start:

    - on the 8th day of the month? or
    - on the first Monday after the 1st of the month?

  6. #6
    Registered User
    Join Date
    07-28-2009
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Weekly Reports

    week 1 being 3/8/09 - 7/8/09
    week 2 being 10/8/09 - 15/8/09
    etc...

    i want the rule to realise if date falls between 3/8 and 7/8 it will go to sheet 2 if it falls between 10/8 and 15/8 goes go shet 3... hopefully makes a little more sense now??

  7. #7
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Weekly Reports

    no, sorry, it does not make more sense.

    if monday, 3-aug-09 is week 1, that means that Sat 1-aug-09 is the start of the week. Fine.

    Given that a week has 7 days, week 2 would span from sat 8-aug-09 to fri 14-aug-09.

    Sat 15-aug-09 would be week 3, but you want it in week 2. That does not gel.

    please explain

  8. #8
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Weekly Reports

    This formula

    =INT(((DAY(A2))-WEEKDAY(A2,2)+7)/7)

    will return a week number for the date in cell A2.

    A new week count will start on a Monday. If the first week of the month does not include a Monday, that week will be week # 0 and the following Monday will be in week 1.

    That's the best I could do. Maybe the formula gurus can come up with something more sophisticated.

    All thunk out now.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1