Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Calc # Weeks Based on Date Cell (2003)

    Is there a way to calculate the number of weeks in a month based on a date cell?

    E.g. 8/31/05 = 5 weeks or 4 full weeks and 1 partial week
    thanks
    christine

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calc # Weeks Based on Date Cell (2003)

    say your date is in cell A1
    In an empty cell, type =DAY(A1)/7

    Does that do what you want?

  3. #3
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Calc # Weeks Based on Date Cell (2003)

    well, duh!

    I was trying to make this so much more complicated that it was. I should have known that!
    thanks
    christine

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calc # Weeks Based on Date Cell (2003)

    Just realized, though, that this really only works if the date in the cell is the last day of the month. Otherwise it only tells you how far into the month you are...Don't know if that will impact what you're doing or not.

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

    Re: Calc # Weeks Based on Date Cell (2003)

    Bans solution works provided the source cell is the last day in the month, which is easy to ceate. If that isn't always so,

    =DAY(DATE(YEAR(A1),MONTH(A1)+1,0))/7
    -John ... I float in liquid gardens
    UTC -7ąDS

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

    Re: Calc # Weeks Based on Date Cell (2003)

    I'm sure you can figure out the rest! <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calc # Weeks Based on Date Cell (2003)

    For what it is worth, a month can not have five weeks. 7 times 5 is 35, and no month has more than 31 days. A month always has four full weeks, and if it is not February in a non leap year, part of a week. A month can have five of whatever day the 1st, 2nd, or 3rd falls on. So, what is your definition of a month with five weeks?
    Legare Coleman

  8. #8
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Calc # Weeks Based on Date Cell (2003)

    I was trying to count the fact that a month could cross a 5th week, like the month of September.
    thanks
    christine

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calc # Weeks Based on Date Cell (2003)

    All months, except for February in non-leap years do that.
    Legare Coleman

  10. #10
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Calc # Weeks Based on Date Cell (2003)

    If you are counting partial weeks plus full weeks, then
    Nov 97 has 2 partial weeks + 4 full weeks = 6
    Dec 97 has 2 partial weeks + 3 full weeks = 5
    Jan 98 has 1 partial week + 4 full weeks = 5
    Feb 98 has just 4 full weeks = 4
    Mar 98 has 1 partial week + 4 full weeks = 5
    Apr 98 has 2 partial weeks + 3 full weeks = 5
    May 98 has 2 partial weeks + 4 full weeks = 6

    Assuming the date in A1, a formula for this would be:
    =IF(AND(EOMONTH(A1,0)-EOMONTH(A1,-1)=28,WEEKDAY(EOMONTH(A1,0))=7),4,IF(AND(EOMONTH(A 1,0)-EOMONTH(A1,-1)=30,WEEKDAY(EOMONTH(A1,0))=1),6,IF(AND(EOMONTH(A 1,0)-EOMONTH(A1,-1)=31,WEEKDAY(EOMONTH(A1,0))<3),6,5)))

    which reads if there are 28 days in the month and it begins on Monday, then there are 4 weeks; otherwise
    if there are 30 days in the month and it begins on Saturday, then there are 6 weeks; otherwise
    if there are 31 days in the month and it begins on Friday or Saturday, then there are 6 weeks; otherwise
    there are 5 weeks!
    That wins an ugly formula prize! --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Calc # Weeks Based on Date Cell (2003)

    Since to use EOMONTH requires the Analysis toolpack, why not use WEEKNUM as well. I think you will get the same answer with the "prettier" formula:
    <pre>=WEEKNUM(EOMONTH(A1,0))-WEEKNUM(EOMONTH(A1,-1)+1)+1</pre>


    Steve

  12. #12
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Calc # Weeks Based on Date Cell (2003)

    Much, much, much nicer. For some reason I saw EOMONTH but not WEEKNUM.
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

Posting Permissions

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