Excel - It's A Matter Of Time
Date-Oriented Calculations In Your Favorite Spreadsheet
Virtually all business professionals use Excel to some degree, and this is particularly true for accounting and financial professionals. One common issue facing these professionals is the need to create calculations, such as accruals, deferrals, or depreciation, that depend on the passage of time. And, unfortunately, this is when things get a bit dicey. Specifically, date-oriented calculations can be challenging for a myriad of reasons. One such reason is that many professionals who need to generate such calculations are unaware of Excel’s terrific capacity to automate these types of calculations. In this article, you will learn how you can quickly and easily create calculations in Excel that depend on the passage of time.
What Is A Date In Excel?
Where should we start? At the beginning, of course, and for date-oriented calculations, that means recognizing that dates in Excel are simply numbers formatted to look like dates. Recognizing this fundamental fact puts you in a position to create Excel formulas that recognize the passage of time.
Try this: enter the numeral “1” (without the quotation marks) into a blank cell in an Excel workbook. After pressing Enter to accept your entry, change the cell format to a date format. To do so, right-click on the cell to open the Format Cells dialog box, click Date, and choose the first formatting code as shown below in Figure 1.
Upon applying the format, Excel transforms the entry in the cell so that it appears to be a date, when in reality, it is a number formatted to look like a date. If you subsequently enter “46000” into the cell that contains “1”, Excel changes the appearance of the number to “12/9/2025”. As stated above, dates in Excel are simply numbers formatted to look like dates.
Notably, if you enter what Excel interprets as a date, it will automatically convert the entry to a number and apply a date format to the cell. For example, if you enter “6/30/2025” into a cell that does not have a date format applied to it, Excel will do two things with your entry when you press Enter. First, Excel will convert the entry to a number. In this example, if the entry is “6/30/2025”, Excel will convert it to 45,838. Second, Excel will change the format in the cell to a date format, so the number appears to be a date.
So, What Is Date Arithmetic?
We’ll use the phrase “date arithmetic” to refer to Excel’s capability to calculate the amount of time that passes between dates. For example, suppose your company took out a loan on June 18, 2024, and the terms of the note are as follows:
- $300,000 principal;
- Two-year term with a balloon payment at the end of the term; and
- An annual interest rate of 6%.
Furthermore, suppose you wanted to calculate the accrued interest on the loan as of December 31, 2025. You can use Excel’s date arithmetic feature to easily calculate the amount of interest. Specifically, you could use the formula in cell C7 of Figure 2 to complete the task.
Understanding The Date-Oriented Formula
Specifically, we can break down the formula as follows:
- The “(C4-C3)” reference in the formula calculates the number of days between 6/18/2024 and 12/31/2025. As info, that calculated value is 561 days.
- The “(C6/365)” reference calculates the interest rate at a daily level.
- Upon multiplying the principal amount in cell C5 by the daily interest rate by the number of days elapsed between the two dates, we arrive at an accrued interest amount of $27,665.75.
Note that you could replace the entry in cell C4 of Figure 2 with an =TODAY() function. Upon doing so, Excel will update the Calculate As Of Date value in cell C4 to the current date whenever the workbook opens. Using this technique, you can create date-oriented calculations that update automatically whenever you open a workbook. The revised formula might resemble the one shown in Figure 3.
Of course, the significance of building the formula with a dynamic date function, such as =TODAY, is that the formula will automatically recalculate every time the workbook recalculates, which means you can place these types of calculations on autopilot!
Summary
Many business professionals – especially those in accounting and finance environments – routinely need to make calculations that depend upon the passage of time. PTO accruals, interest calculations, revenue recognition, and depreciation expense are but a few examples of the need for date-oriented calculations. For those who recognize the true nature of dates, these calculations can be automated easily, as you have learned in this article. Those who choose not to adopt this form of building date-oriented formulas will be resigned to making manual updates of their calculations on an ongoing basis. Which choice will you make?
At K2 Enterprises, our commitment lies in providing unwavering support and expert instruction to CPAs. Explore the wealth of resources on our website, where you’ll find valuable insights on selecting the most suitable accounting software, ensuring your firm is equipped with the right tools for the journey ahead. If you work in accounting or finance, K2 Enterprises provides continuing education programs to enhance your skills and credentials. Need help learning how to solve your business’s accounting technology needs and selecting the right software for accounting or CPA Firms? Visit us at k2e.com, where we make sophisticated technology understandable to anyone through our conferences, seminars, or on-demand courses.
