Friday, June 9, 2017

Microsoft Excel Advanced #6 - Extracting a Value on a Specific Day and Month for Multiple Years

This post will demonstrate how to extract a value on 01 April for multiple years in Microsoft Excel.  I used the coding example from the following link to guide the development of the needed equations.

Example from stackoverflow.com

Below is the data that I am using for this example.  It is in columns A and B of the Excel worksheet.  There are three instances where the date is 01 April.




I write an IF statement that checks if the month is April and the day is 1.  If it is, a number is added to column C that represents the number of occurrences of 01 April that have occurred up to that row.



This IF statement is added to all appropriate rows in column C.  The results are shown below.




I then use the following equation in column E to extract the date if there is a value in column C.



I then use the following equation in column F to extract the value if there is a value in column C.




The results of applying these equations is shown below.



The explanation of the methods used above are as follows (from Excel help menu).
















No comments:

Post a Comment