Help with date columns (Excel XP SR1)

Topic

New Reply

In column B of a worksheet, there will be a 379-day range of dates, date_range.

In column C, I need the formulas that will retrieve and list just the Sunday dates from date_range. 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 date_range.

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.

Reply | Quote

Replies

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.

Reply | Quote

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: =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))))))

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.

Reply | Quote

Ricky,

Given your new specs,

in C3 enter:

=B10-MOD(B10-1,7)

The rest stays the same as in my previous post.

Aladin

Reply | Quote

Worked perfectly and was a better solution than nesting all those If statements. Thanks for the help – problem solved!

Reply | Quote

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)

( 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:-(CELL("row",Dates!B3)-1)

SundayRecs in B2 with

=B1

and MonthEndRecs in B3 with

=MATCH(9.99999999999999E+307,Dates!D: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

Reply | Quote