How to select the last day of selected months in daily data in EXCEL

Question

I have daily data from 01-Jan-2005 till 29-Dec-2017. I want for each year to select the last day of March, June, September, and December, alongside their respective data. Part of the data:

   Date    Variable
30-Mar-2005 1.2943
31-Mar-2005 1.2964
1-Apr-2005  1.2959
4-Apr-2005  1.2883
5-Apr-2005  1.281

I.E: For 2005, I want the dates of 31-March-2005, 30-June-2005, 30-September-2005, and 30-Dec-2005. Desired output:

Date        Variable
31-Mar-2005 1.2964
30-Jun-2005 1.9859
30-Sep-2005 1.2233
30-Dec-2005 1.2814

I currently have the build in excel formulas (i haven't installed any other plug-ins etc).

More specifically: on the left i have the data, and on the right the desired output.

Can you post example of data and desired output? Please don't post pictures. — zipa, Jul 20 '18 at 10:28
For one input you want four outputs? Please be super clear about what data is in the input cell and what data is in the output cell. — Enigmativity, Jul 20 '18 at 10:31
Now you're making it more confusing. Please be super clear about what data is in the **input cell** and what data is in the **output cell**. Provide several such examples. — Enigmativity, Jul 20 '18 at 10:33
Will you always have a data point for the actual last date of the month? If so it's trivial to make a series of the last day of each month and then just do a simple `vlookup` — Dan, Jul 20 '18 at 10:34
@Enigmativity I don't understand what you mean. I have daily data which start from 1st of January 2005 up to 27th of Dec-2017. That's like 3300 observations that i have. But instead, i look for 4 dates out of each year. That is the last day of 4 of the months. — Hercules Apergis, Jul 20 '18 at 10:34
@HerculesApergis - Tell me what date is in cell, say, B14, and what the result of the formula you want is in C14. Provide samples for B15/C15, B16/C16, etc. A table with a `Variable` column that you haven't explained is confusing. — Enigmativity, Jul 20 '18 at 10:37
@HerculesApergis - I want a clear set of inputs and the clear set of outputs that relate one cell in the input to one cell in the output. Have I explained it better now? — Enigmativity, Jul 20 '18 at 10:37
@Dan I do not want for each month. Only for the selected four. — Hercules Apergis, Jul 20 '18 at 10:38
@FoxfireAndBurnsAndBurns This is because the data do not have an observation for the 31st. — Hercules Apergis, Jul 20 '18 at 12:07
@HerculesApergis so then it's even easier, it's just a plain and simple `vlookup`. Is there any reason that `vlookup` doesn't solve this for you? — Dan, Jul 20 '18 at 12:24
@Dan I am unaware of how it will return the output I am looking for, since I haven't used it before. Could you make an example, please? — Hercules Apergis, Jul 20 '18 at 12:37
@HerculesApergis What do you mean with *the data do not have an observation for the 31st*? You mean that 31st **is not** in the list? Or do you mean that there is no data because any other column related to 31st have 0 values? It's important — Foxfire And Burns And Burns, Jul 20 '18 at 13:33
@HerculesApergis - "More specifically: on the left i have the data, and on the right the desired output." - On the left of what and the right of what? — Enigmativity, Jul 20 '18 at 13:36
@HerculesApergis no, the help docs will have plenty of examples. You first need to demonstrate that you've tried. `vlookup` solves this and is an incredibly common and basic Excel formula. Read the docs, try make it work, and then post what you've tried if you still can get it — Dan, Jul 20 '18 at 13:36
@HerculesApergis - Let's say I have a function called `f`. I call `f` like this: `y = f(x)`. I want to know what `x` is and what `y` is that you need. If there's more than one `f` that you need, that's fine, but for each `f` I want to know a series of pairs of `x` and `y` that you'd pass through each `f` to get the result you want. I don't know how much clearer than that I can make it. — Enigmativity, Jul 20 '18 at 13:38
@HerculesApergis - Oh, I just read Ron's answer. Are you asking for a simple `VLOOKUP` of the data on the left to give you the data on the right? — Enigmativity, Jul 20 '18 at 13:39
@Enigmativity If you look at the pic that I posted on the left are the data (columns A,B,C), and on the right the results (columns E,F,G). However on the picture I show only the first four observations for 2005. The same is for 2006,2007....up to 2017. — Hercules Apergis, Jul 20 '18 at 14:35

Ron Rosenfeld · Answer 1 · 2018-07-20T13:35:06.177

I converted a list of Euro conversions into a Table and used structured references. But you can use normal range references if you prefer.

In some other table, enter the following formula, where $A$45 refers to the first quarter ending date in your data table.

F2: =IF(EOMONTH($A$45,(ROWS($1:1)-1)*3)>MAX(Table1[Date]),"",LOOKUP(2,1/(EOMONTH($A$45,(ROWS($1:1)-1)*3)>=Table1[Date]),Table1[Date]))

In the adjacent column, enter the formula:

G2: =IFERROR(VLOOKUP(F3,Table1,2,FALSE),"")

And fill down until you get blanks.

(in my sample table, the last date is 1/27/2006 so the last included "End date" is 12/30/2005, there being no data for 12/31/2005)

score 1 · Accepted Answer · answered Jul 20 '18 at 13:55

Not sure if this is going to work to you, but anyways.

Looks like you always look at the last day of months March, June, September and December on a specific year (in example, 2005).

But you are not looking for the last natural day of each month. You want the last day of each month that appears in your data (in example, that explains why you use 30 december 2005 instead of 31, because there is no 31).

In Excel, dates are numbers. The more you go in the future, a bigger number is related. Knowing this, you can get the date for each month just looking the MAX value of a range of dates.

But first, you need to define the range of dates, using 2 conditions:

Month of date must be March, June, September and December
You want dates for a specific year (in example, 2005).

To get this, you need an array formula. My formula gets the max day of a specific month and year. To test it, in my Excel I did a dates series, starting in 01/01/2005 and done in 31/12/2017. I deleted manually 31/12/2005 because that date has no data.

In cell I4, just type the year you want to check. The formula will get he last day of months March, June, September and December of that year.

My array formula is:

=MAX(IF(MONTH(IF(YEAR($A$4:$A$4750)=$I$4;$A$4:$A$4750))=3;$A$4:$A$4750))

IMPORTANT!: Because it is an array formula, you will need to type it as usual, and then, instead of pressing Enter press CTRL+SHIFT+ENTER

You need 4 times this formula. Just change the 3 (March) for the number of the month you need (6,9 and 12).

Now that you have the dates, you just need a VlookUp to get the value you want.

=VLOOKUP(G5;$A$4:$B$4750;2;false)

If I change the year value, i get those new values:

If you want to check the file. I uploaded an example to Gdrive, so you can download if you want.Download

Anyways, try to adapt this formulas to your needs.

I am using the `=ARRAY_CONSTRAIN(ARRAYFORMULA(MAX(IF(MONTH(IF(YEAR($A$4:$A$3303)=$I$4,$A$4:$A$3303))=3,$A$4:$A$3303))), 1, 1)` ,as in your example, my excel gives: #NAME? ! BUT when i try it on the google excel it then works...! — Hercules Apergis, Jul 20 '18 at 14:57
I understand, but have been checking it over and over again. The same syntax worked perfectly to the google excel. — Hercules Apergis, Jul 25 '18 at 20:45

score 0 · Answer 3 · edited Mar 24 '20 at 12:16

So, there are two cases

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 example, 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!
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 example, 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.

How to select the last day of selected months in daily data in EXCEL

3 Answers3