The first step is to generate a list of months. Although we want the user to see the months as Jun-18, Jul-18, Aug-18, etc…, we will start by entering in the months as actual dates.
Select cell H14 and enter the following date:
Using the Fill Series handle, drag down to cell H17.
Because we don’t want a sequence of days, we want a sequence of months, select the Fill Series Options button and select Fill Months.
Now that we have our months, we will format the months by pressing CTRL-1 (or right-click Format Cells.) From the Category list, select Date and then select the “Mar-12” format and click OK.
The result should appear as follows.
To calculate the Revenue, we are presented with a bit of a problem. We have the start date of each month (H14:H17), but what we lack is the end dates for those months.
To solve this problem, we will use an often overlooked yet quite useful function called EOMONTH. The structure of EOMONTH is…
The EOMONTH function accepts a date as its first argument and then a number as it’s second argument. The number defines how may months (forward or backward in time) you want to travel. Not only does it “travel” to the requested month, but it selects the last day of the resultant month.
By using 0 (zero) as the number of months to travel, we will remain in the current month but move to the last day of that month.
Now we will create the formula using SUMIFS and the EOMONTH functions. In cell J14 enter the following formula:
The result for June is as follows.
Use the Fill Series handle to fill the formula from cells J14 through J17.