excelselectdayofmonth

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

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.

enter image description here

Solution

  • 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:

    1. Month of date must be March, June, September and December
    2. 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.

    enter image description here

    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:

    enter image description here

    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.