Results 1 to 3 of 3
  1. #1
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Date Formula (Excel XP)

    I need help with a date formula, please.

    I have used data validation to insure that the user enters a date in cell K4. The cell has a custom format of mm/dd/yyyy. Let's assume that the user has entered <font face="Comic Sans MS"><font color=448800>11/29/2002</font color=448800> </font face=comic> into K4.

    In cell B11, I need a formula that will result with: <font color=blue>Week 1 (12/01/02)</font color=blue>.
    Cell B12 would then need a formula to produce: <font color=blue>Week 2 (12/08/02)</font color=blue>. ...Column B would extend down to cover Week 14

    So, in summary, the formulas in the B column would need to produce week ending dates (Sunday) based on the date entry in cell K4. The entry in K4 may be any date. In fact, it could even be a Sunday date, in which case the formula in B11 would need to be that date.

    I'm assuming that the B11 formula would have to include a <font color=red>=concatenate</font color=red> statement. And because of that, I'm also assuming that B12 could not use: <font color=red>B11+7</font color=red>. I guess the formula in B11 would first have to evaluate if the K4 date is a Sunday, and if so, use that date - and if not, it would have to identify and use the next Sunday date.

    Ideas?
    - Ricky

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

    Re: Date Formula (Excel XP)

    The formula for cell B11 is
    <pre>=TEXT(ROW(B11)-ROW(B$11)+1,"""Week ""0")&
    TEXT($K$4+MOD(8-WEEKDAY($K$4),7)+(ROW(B11)-ROW(B$11))*7,""" (""mm/dd/yy"")""")</pre>

    Note, the above should be one line, but there is not enough room. You can autofill the formula down for subsequent weeks. HTH --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>

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Thank you

    Formula worked to perfection and I appreciate it. <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>
    - Ricky

Posting Permissions

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