We recently closed Fiscal Year 2014 and are now in Fiscal Year 2015, you can imagine how much work was on our finance team’s plate especially with the beloved auditors roaming in our offices! If you haven’t yet approached your financial year-end, this tip will help you ensure that you don’t miss it by helping you to easily calculate the end of your financial period.
As an accountant you may want to calculate the end of a period for a given start date and the EOMONTH function can be used. The EOMONTH function returns a serial number representing the last date of the month. The number of months before or after a start date should also be specified.
If, like us, you revisit your company strategy every fiscal year, you may get some inspiration on key focus areas for FY2015 from our Finance and Strategy Directors’ blog article: Strategy – the Sage Alchemex way (with a little help from Thula Thula and Elon Musk).
Note: Download the workbook to practice this exercise
Applies To: Microsoft Excel 2010 and 2013
- With reference to the practice workbook, select cell B2.
- Type =E and double click EOMONTH from the drop down list.
3. Select cell B2.
4. Add a comma(,) and enter 11.
- The 11 refers to the number of months after the start date in cell B2.
- Enter a negative value for the number of months before a start date.
5. Press Enter.
6. A serial number representing the end of a period will be displayed.
7. To convert the serial number to a date:
a. Right click cell B4.
b. Select Format Cells .
c. Select Date.
d. On the right hand side; select the yyyy,mm,dd date format.
e. Select OK.
Conclusion
The end of the financial period has been calculated, with an easy but powerful formula.