# Thread: Help with date columns (Excel XP SR1)

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

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

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

=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.

5. ## Re: Help with date columns (Excel XP SR1)

Ricky,

in C3 enter:

=B10-MOD(B10-1,7)

The rest stays the same as in my previous post.