Formula used to Calculate Date Range

Wow...I can't believe it's been almost a month since the last posting.  Even those there's been a lot going on at work (i.e. new projects, a new customer, existing projects) there's just no excuse to not keep up with these postings since there are so many people reading the blog.  I know this only because of the stats tracked by the site not because of any feedback or comments because there are none of those.  


So to pick back up where we left off we'll take a look at the formula used to allow a user to select a fiscal year and the workbook will display data based on the selection.  The user wanted to be able to select a year from a drop down and have data display for that period for both the current and previous fiscal year.  Since that posting I've had a similar request but this user wants to be able to select a year and if it's a previous year display data for the entire year but it the current year is selected display data through the last full month.  The formula used for the beginning of each of these is the same.


We'll begin with a look at the layout of the dates for a five year period (below).  First we need a formula for the "Begin Date" of the current fiscal year in cell R6.  The formula used in this example is:
=IF(MONTH(TODAY())<3,DATE(YEAR(TODAY())-1,2,1),DATE(YEAR(TODAY()),2,1))
Since this customers fiscal year runs February through January then we have no current fiscal year data until we are in the month of March so the formula if the current date is less than month 3 (i.e. March).  After March it will display the current year.

 The "End Date" for the current year is created with this formula:
=DATE(YEAR(TODAY()),MONTH(TODAY())-1,1).  This formula displays the first day of last month.


Next we want to display the Begin and End dates for the previous four fiscal years.  For the "Begin Date" in cell R5 we use this formula =EOMONTH(R6,-12) and then copy and paste the formula in range R2:R4.  If you're not familiar with this formula the arguments for it are =EOMONTH(base, offset) where the base value is a date from which the function should do its calculations, and the offset is a number that indicates how many months from the base date should be used.  The offset can be either positive or negative. 


For the "End Date" in cell S5 the same formula is used except our offset is a negative one (i.e. =EOMONTH(R6,-1)).  This formula is then copied and pasted to Range S2:S4.


Now that we have our table of dates we need to convert the dates into formats that can be used for the date selection as well as formats that can be passed to MS Query to return the correct data.


For the current fiscal year in cell T6 we use =YEAR(R6).  This formula is copied and pasted into range T2:T5.  The formula used for the Begin date in cell U6 is =YEAR(R6)*100+MONTH(R6).  This formula is also copied and pasted into range R2:R5.  For the End dates the formula is =YEAR(S6)*100+MONTH(S6).  As previously this formula is copied and pasted in range S2:S5.

The range T2:T6 is Named and used with either Data Validation or as in this case with a List Box from the Forms Control selection.  The Begin and End dates are used as the criteria for the MS Query.


In the case above where the customer wanted the MS Query to return data of any fiscal year selected only through the previous month related to the run date of the report we change the formula in cell S5 to =EOMONTH(S6,-12) and then copy and past to range S2:S5. This results in the table below:

As always, if you have any questions or comments you can leave them here as a comment or email me at randomexcel@att.net.