# Thread: More Date Stuph! (Excel XP SR1)

1. ## 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>

2. ## 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. ## 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...

4. ## 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. ## 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.

6. ## 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.

#### Posting Permissions

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