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

    More Date Stuph! (Excel XP SR1)

    Okay, so now I 'm asking for the impossible! But you all have solved the impossible before...

    I have two date ranges: One is a chronological list of Sunday dates (54 Total), The other is a list of EOMs (12 Total, making a full calendar year).

    I need a third list that will combine those two, sort it and perhaps remove the duplicates.!!!?

    See, I told you...impossible. I've attached the problem so you could visually see that there is a method (or meaning) to my madness. <img src=/S/confused.gif border=0 alt=confused width=15 height=20> <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>
    Attached Files Attached Files
    - Ricky

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More Date Stuph! (Excel XP SR1)

    An array formula in the format (Sundays=EOMS)*Sundays will give you an array of 12 possible dates where the date is equal.
    The order of the dates will be dependent on the order of the Sundays list.
    There will be no duplicates.

    I have assumed that the range of Sunday dates is called Sundays and the EOMs is called EOMS.

    Dont forget to enter the formula using Ctrl-Shift-Enter to enable the array processign features. then copy the formula down for 12 rows.


    HIH

  3. #3
    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: More Date Stuph! (Excel XP SR1)

    They always told me that the 4-4-5 system was better for sales and food cost. I agree with you that one month this year compared with one month last year is a better guide. Anyway, attached is your book with links to the dates in the cells you wish sorted. The macro that does the sorting is SortDates, you can run it on the change event or from a button. HTH...
    Attached Files Attached Files

  4. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: More Date Stuph! (Excel XP SR1)

    Hi Ricky,

    You have all the formulas for the Sundays and the End of Months. To get the list to include both Sundays and EndOfMonths you need to add an IF to the front of each formula saying that if the month in the current formula is not the same as the month in the one above then use the Endof Month formula, instead of the next Sunday one.

    Then you also have to consider that the next date will only be 7 days forward, if the previous date was not a Sunday, otherwise it will be 14 days from the next previous date.

    Something like:

    In E8 =IF(Month(E7)<>Month(E7+7), "Insert EndOfMonth formula here",If(E7<>"Sun",E7+7,E6+14))

    Good Luck!

  5. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: More Date Stuph! (Excel XP SR1)

    If the first date is in A1, insert the following formula into A2 and copy it down.
    =MIN(DATE(YEAR(A1),MONTH(A1)+1,0),A1+7)+IF(DATE(YE AR(A1),MONTH(A1)+1,0)=A1,8-WEEKDAY(A1),0)
    This fills column A with the dates of Sundays and month ends, and even continues past year's end.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

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

    Re: More Date Stuph! (Excel XP SR1)

    <img src=/S/salute.gif border=0 alt=salute width=15 height=20> Thank's Pod, that was some formula and it worked perfectly.
    - 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
  •