1

I saw this related question, Select each nth row in Excel, and it was very helpful except I need to know which is the last day business day in each month.

Excel workbook with important data circled How do I do this?

Community
  • 1
wizlog
  • 13,573

2 Answers2

2

Here is the formula and proof of concept

Put this in an empty column in row 2:

=A2=MAX(IF((YEAR(A2)=YEAR($A$2:$A$70))*(MONTH(A2)=MONTH($A$2:$A$70)),$A$2:$A$70))

It is an array formula so it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode. If done correctly then Excel will put {} around the formula.

OR this non CSE array formula:

=A2=AGGREGATE(14,6,$A$2:$A$70/((YEAR(A2)=YEAR($A$2:$A$70))*(MONTH(A2)=MONTH($A$2:$A$70))),1)

Enter this normally.

Then copy down.

This finds the max date of the month in the list and returns true if the date in column A is equal to it.

Then filter on all the FALSE and Delete.

enter image description here

Scott Craner
  • 23,868
0

So, there are two cases- Case 1) when you have the exact last days of the month. (Its simpler).

I had a fortnightly data. I adopted this simple and innovative method. From all the dates, I first extracted the day using Day() function. for ex, Day(A1).(Remember, while doing this, do not delete your original date column. Do it in a separate column as this would help you match the dates later). Then I sorted the data using the Day column, just constructed before, in decreasing order. This would place all end dates at first. And then deleted the starting dates which were at bottom. So, now I am left with only end dates but obviously months are not in order. So, create another column extracting just the month and year from the original date column using =MONTH(A1) & "/" & YEAR(A1). Sort the data using this column. And, you are good to go!

Case 2)when you do not have the exact last days, but maximum dates like the one shown above in picture.

In this, while deleting the initial dates, you would have to take care of which date on-wards you need to delete. For, ex, I deleted day 17 on-wards of months with 31 days (including day 17) and day 16 on-wards of months with 30 days (if present) because if there was this date, suppose, 18 April 2018, then this would be the last day of the month as I had a fortnightly data.

Sakshi Agarwal
  • 1