Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    San Jose, California, USA
    Posts
    290
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date problem (2003)

    I'm trying to figure out a formula or function to calculate the first day of a particular whole week in a month. For example, if I wanted to know the first day of the third whole week of June, 2004, I'm looking for a way to produce the result June 20. Since June 1 fell on a Tuesday, the first whole week starts on June 6, the second on June 13 and the third on June 20.

    Does anyone know how to do this?

  2. #2
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Date problem (2003)

    Try the following
    Cell A1 = Year (2004)
    Cell A2 = Month (6)
    Cell A3 = Number of weeks (3)

    =DATE(A1,A2,1)+1-WEEKDAY(DATE(A1,A2,1))+(IF(WEEKDAY(DATE(A1,A2,1))= 1,A3-1,A3))*7

    How it works:

    DATE(A1,A2,1)+1-WEEKDAY(DATE(A1,A2,1))
    This takes the first of the month then subtracts the number of days to get the date for Sunday of that week.

    (IF(WEEKDAY(DATE(A1,A2,1))=1,A3-1,A3))*7
    This works out how many days to add to the first part of the equation.

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Date problem (2003)

    I assume the week starts with Sunday. This should work, test it a bit more than I have:
    <table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>1</td><td align=right>6/1/2004</td><td align=right>=A1+IF(WEEKDAY(A1,2),7-WEEKDAY(A1,2),)</td><td>1st Sun</td><tr><td align=center>2</td><td align=right>
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Date problem (2003)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Andrew

    Apart from Tony and John, I elected to use a User Defined Function implemented via VBA.

    I think that it will give you a wider range of capabilities, such as truely return the full week's starting date. In the case of February and October 2004, the last day happens to be a Sunday, but I am not sure if you want to count this as a full week.

    The code is almost self documented, so if you have any questions, please let me know.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  5. #5
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    San Jose, California, USA
    Posts
    290
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date problem (2003)

    Thanks, Tony. That did just what I want. I must be getting slow in my old age not to figure that out!

Posting Permissions

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