EOMONTH – a wonderful formula that saved me a lot of time and mucking around today!
People say you learn something new every day, and I am really happy when that is true. Today I have been fiddling with an Excel Workbook that is the financial plan for my business. I was provided with one to use and I had issues with it (broken links, formulas picking up values from the wrong cell, and it did not account for the accounting basis or GST registration) so I created my version. As you do…
My version is more automated, you can switch between GST registered and not GST registered by typing yes or no into a single cell and you can choose your accounting method (cash or accrual) by making a selection in a drop-down box and the individual sheets update to incorporate the necessary changes depending on which options you choose. Only input cells are unlocked. You can click on the other cells but you cannot change them. This one incorporates information about your budget as well as your business finances so that you can work out how much you will need to draw down to cover your household expenses.
Super cool. But not really what this post is about.
One of the things I learned during this process is. I had a situation where the start date is mid-month but I want to report monthly. In the past, I have manually edited or typed in the dates for each period on one page and then used a formula to automatically update all other sheets to match. Not a huge drama, but it does take a bit of time and can be a bit error-prone. What I found today is EOMONTH. What this Excel formula does is evaluate a date (in another cell) and populate the cell containing the formula with the date of the last day of the month.
In this sheet, I have the date formatted as mmm but you can change the format quite easily by adding a custom format. On another sheet, I used a mmm-yy format. On that sheet, the dates are dis[laid as Oct-21, Nov-21, Dec-21, Jan-22, etc instead of Oct, Nov, Dec and Jan as they are on this sheet.
So what is the EOMONTH formula and how does it work?
The format for the formula is =EOMONTH(Start_date,Months). For my workbook, I have a page with tables for values like start date and GST rate. All instances of those values within the workbook are added by referencing the cell on that page. That way if any of those values change (I know the GST rate has not changed since its inception. But you never know if and when it will), the change in the value will flow through your workbook automatically just by updating a single cell on the tables page.
The month of the start_date is 0, and the number rises as you move through the months. So in my example, Oct is month 0, Nov is month 1, and Dec is month 2. You can go backwards as well. IN my example, month -1 would be Sep, month -2 would be Aug. Again – how cool is that?
The start_date on my table sheet is 1 Oct 21. The formula I used was =EOMONTH(‘Start Here’!$D$9,0) with ‘Start Here’!$D$9 being the cell with the original start date (Start Here is the sheet that contains my tables).
