BUDGETING TIP OF THE MONTH
Accounting for Time in Excel Based Budgeting
When using Excel for budgeting, it can be difficult to account for monthly time periods and ensure that relevant expenses are input, allocated and prorated correctly by month. Consider using the following formulas:
Prorating an expense in a month based on a specific date:
You have an employee that will start on the 20th of July and you would like to prorate their salary expense for the month.
- Cell B2 has their start date
- Cell C2 has their standard monthly salary
- Formula to use =(DAY(EOMONTH(B2,0))-DAY(B2)+1)/DAY(EOMONTH(B2,0))*C2
This formula will look at the month that is in the start date and then the specific day in that month in relation to the length of the month (28/30/31 days). In our example, this formula will return a value of .39 or 39% of the month of July for the portion worked times the gross monthly salary.
Converting date formats:
If you have your months listed out as January, February, March, etc and are looking to convert these to numeric values, use the following formula:
- Cell A2 is January
- Formula to use =Month(1&A2)
Consider using this formula when referencing the number of months in a given period or to automate a delay in expenses. Ex: Incur an expense for a new computer based on the start date of a new hire or depreciate a fixed asset over a set asset life.
To convert a numeric month from numeric back to text, use the following formula:
- Cell A2 is “1”
- Formula to use=TEXT(DATE(2019,A2,1),"mmmm")
- “mmmm” will return a value of January
- “mmm’” will return a value Jan
Use these and many more Excel functions and formulas to automate your planning process and cut down on time spent manually entered values or carrying forward historical data!
