Filter last working day

[agibsonsw]

Hello. Excel 2003.
I have a column of dates.
Is it possible to create an advanced filter to find the last working day of each month?
Thanks, Andy.

[HansV]

Start by entering a list of holidays in a column, and name the range Holidays.

Let's say that your column of dates is column A, with a header in A1 and the first date in A2.

For the criteria, use a 2 cell range. Leave the top cell blank, or enter a word that is not a field name in the data table, and enter the following formula into the cell below it:

=WORKDAY(DATE(YEAR(A2),MONTH(A2)+1,1),-1,Holidays)=A2

The expression WORKDAY(DATE(YEAR(A2),MONTH(A2)+1,1),-1,Holidays) returns the last working day in the month of the date in A2.
The formula compares this to the date in A2, and returns TRUE if A2 is the last working day in the month, and FALSE otherwise.

You can now apply advanced filter with the list of dates as data range and the 2 cell range as criteria range.

See the attached sample workbook.

LastWorkdays.xlsx

[agibsonsw]

Thank you. I shall study this. Andy.