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

    Help with date columns (Excel XP SR1)

    In column B of a worksheet, there will be a 379-day range of dates, <font color=blue>date_range</font color=blue>.

    In column C, I need the formulas that will retrieve and list just the Sunday dates from <font color=blue>date_range</font color=blue>. Keep in mind that the actual dates in column B are consecutive and will begin on the 25th of December of some year.

    Column D will need formulas that will retrieve and list the month ending dates from <font color=blue>date_range</font color=blue>.

    The list that are generated in C and D have to be useable list (no blank cells), they will later be used as the source for list boxes.

    I've attached a sample workbook that should clarify what I'm looking for. Any help would be greatly appreciated. Thanks.
    Attached Files Attached Files
    - Ricky

  2. #2
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with date columns (Excel XP SR1)

    Hi Ricky,

    What relationship is there between B10 and column C in your example? In other words, are you looking to find the first Sunday after the date entered into B10 or the previous Sunday? (looks like the next Sunday from the example).

    Once you establish the first date in column C, you can extend the list in column C using: (in cell C4) '=C3+7'

    You can use the EOMONTH function based on the entry in B10 to return the last day of the month in column D, depending upon the value in B10. The EOMONTH function is, I believe, available in the Analysis Tool Pak Add-In.

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

    Re: Help with date columns (Excel XP SR1)

    Thanks - I don't know why I didn't think of that. Actually, the first date in the C (Sunday) column should be the Sunday preceeding B10 ...

    The first date in the C column comes as a result of this formula: <font color=blue>=IF(WEEKDAY(B10,2)=1,B10-1,IF(WEEKDAY(B10,2)=2,B10-2,IF(WEEKDAY(B10,2)=3,B10-3,IF(WEEKDAY(B10,2)=4,B10-4,IF(WEEKDAY(B10,2)=5,B10-5,IF(WEEKDAY(B10,2)=6,B10-6,B10-7))))))</font color=blue>

    Any thoughts on column D, other than EOMonth - it's very possible that other users of the sheet would not have the analysis tool pak installed.

    The new attached sheet has the C column completed with new formulas.
    Attached Files Attached Files
    - Ricky

  4. #4
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with date columns (Excel XP SR1)

    Ricky,

    In C3 enter:

    =B3+IF(1<WEEKDAY(B3),7-WEEKDAY(B3)+1,1-WEEKDAY(B3))

    In C4 enter and copy down:

    =C3+7

    In D3 enter:

    =DATE(YEAR(B3),MONTH(B3)+1,0) [ or: =EOMONTH(B3,0), which requires the Analysis Toolpak add-in ]

    In D4 enter and copy down:

    =IF(COUNTIF(B:B,D3+1),DATE(YEAR(D3+1),MONTH(D3+1)+ 1,0),"")

    I created an additional sheet, named Admin, in order to define named dynamic ranges:

    (a) DATES, which refers to:

    =OFFSET(Dates!$B$1,0,0,DateRecs,1)

    ([img]/forums/images/smilies/cool.gif[/img] SUNDAYS, which refers to:

    =OFFSET(Dates!$C$1,0,0,SundayRecs,1)

    MONTHENDS, which refers to:

    =OFFSET(Dates!$D$1,0,0,MonthEndRecs,1)

    where DateRecs is computed in B1 in Admin with

    =MATCH(9.99999999999999E+307,Dates!B:[img]/forums/images/smilies/cool.gif[/img]-(CELL("row",Dates!B3)-1)

    SundayRecs in B2 with

    =B1

    and MonthEndRecs in B3 with

    =MATCH(9.99999999999999E+307,Dates!D)-(CELL("row",Dates!D3)-1)

    You can use SUNDAYS and MONTHENDS as source for the list boxes that you want to set up.

    Aladin
    Attached Files Attached Files
    Microsoft MVP - Excel

  5. #5
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with date columns (Excel XP SR1)

    Ricky,

    Given your new specs,

    in C3 enter:

    =B10-MOD(B10-1,7)

    The rest stays the same as in my previous post.

    Aladin
    Microsoft MVP - Excel

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

    Re: Help with date columns (Excel XP SR1)

    Worked perfectly and was a better solution than nesting all those If statements. Thanks for the help - problem solved!
    - 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
  •