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,277
  • 24
  • 77
  • 116
  • What do you have so far? From the pic, assuming this is Excel 2013/2016? – CharlieRB Jun 06 '16 at 20:06
  • The answer to this can be easy or slightly more complicated depending on what you mean by "last business day of month". Do you simply mean Monday - Friday = business day or do you have to take into account holidays as well? If holidays then, which ones? If just Monday - Friday then you can do this using the =WEEKDAY() function – Eric F Jun 06 '16 at 20:08
  • @CharlieRB I have the opening and closing price of a stock (I hid a bunch of rows), I only want the opening and closing data for one day per month (preferably the last one that the market is open). – wizlog Jun 06 '16 at 20:09
  • @EricF Holidays as well. The data I have is from Google Finance, so only days where the stock market (in the United States) is open would have a row. – wizlog Jun 06 '16 at 20:09
  • I can see that. What I want to know is what have you done to try to solve this yourself. This isn't a script writing service, so we want to know what research you have done and where you are stuck. – CharlieRB Jun 06 '16 at 20:11
  • Well I believe that Google Finance simply doesn't record an entry for holidays if I am correct? If that is true then you can simply find the greatest day for each month from the generated list – Eric F Jun 06 '16 at 20:11
  • @CharlieRB I tried filtering all the days in the months less than 25 to remove the bulk of the data, then I tried using mods to eliminate the rest. the problem though is that in each month the last business day isn't nearly always the same. – wizlog Jun 06 '16 at 20:16
  • in a blank column `=A2=MAX(IF((YEAR(A2)=YEAR($A$2:$A$70))*(MONTH(A2)=MONTH($A$2:$A$70)),$A$2:$A$70))` then filter on FALSE. This is an array formula so Ctrl-Shift-Enter when leaving edit mode. – Scott Craner Jun 06 '16 at 20:25
  • @ScottCraner, what do I enter for the last two arguments of the if statement? Is that where you suggest I filter on false? – wizlog Jun 06 '16 at 20:40
  • You would just put the above formula in an empty column in Row 2. Hit Ctrl-Shift-Enter **instead** of Enter then copy it down. It will fill the column with TRUE/FALSE. TRUE would be the last day of that month recorded. False would be all the others. – Scott Craner Jun 06 '16 at 20:44
  • @ScottCraner Excel is telling me there's a problem with the formula. It highlights "$A$70" at the very end of the formula and won't let me leave the cell without fixing the problem. – wizlog Jun 06 '16 at 20:48
  • do you use `;` instead of `,` in your formulas? Another thing to try is; sometimes when using the comments invisible characters are put in, try typing the formula instead of copy paste. – Scott Craner Jun 06 '16 at 20:50

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
  • 22,693
  • 3
  • 21
  • 25
  • Which version of excel are you using? – wizlog Jun 07 '16 at 02:39
  • 2013 but this will work in just about any age. – Scott Craner Jun 07 '16 at 02:45
  • I'm tried in excel 2013 and 2016. I'm really sorry, but this isn't working at all. It is returning false for every cell. – wizlog Jun 07 '16 at 13:19
  • Do you see the `{}` around the formula? If so then check the data in column A to make sure they are true dates and not text that looks like dates. Excel stores true dates as numbers not text. – Scott Craner Jun 07 '16 at 13:53
  • @wizlog see edit I added a second formula that does not require the Ctrl-Shift-Enter. Try that. If that does not work look at the data, because there is a problem with it. – Scott Craner Jun 07 '16 at 13:58
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
  • 1