Excel - It's A Matter Of Time Part 2
In a previous post, I wrote about calculations in Excel based on the passage of time. That article describes how you can take advantage of date-oriented calculations in Excel to automate many types of computations. In case you missed it, you can read the original article by clicking here. Today, let’s continue down this path by focusing on calculations involving hours and minutes, instead of days and years.
Understanding Excel-Based Times
To understand times – hours and minutes – in Excel, begin by recognizing that Excel bases times on a 24-hour clock that starts at midnight. Thus, with 24 hours in a day, if you divide one hour by 24 hours, you quickly understand that each hour consumes 4.1666% of a full day. With that knowledge, if the time you’re considering is 6:00 a.m., for example, the day is 25% complete. Thus, in Excel terms, 6:00 a.m. is represented by the decimal value 0.25. Likewise, if the time you’re considering is noon, the day is half over. Therefore, 0.50 represents noon. In all cases, times are represented by a decimal value, although that value can have a time format applied to it.
Below, Figure 1 provides the numerical equivalent for each hour, based on the percentage of the day that has expired.
Calculations Based On The Passage Of Time
As stated, dates and times are simply numbers (including fractional values) that we format to look like dates and times. Thus, we can easily use Excel to automate calculations based on the passage of time. For example, suppose a team member who tracks billable hours started working on a client’s project at 9:15 a.m. Further, suppose they completed their work at 12:20 p.m., and we need to know how much time to bill for. Based on Excel’s date and time capabilities, this is a relatively easy task. To solve the problem, type “12:20” into a cell and type “9:15” into another cell.
By default, Excel “sees” that you are entering items that appear to be time entries. As such, Excel converts them to their numerical values of 0.513888 for the 12:20 p.m. entry and 0.3854166 for the 9:15 a.m. entry. From here, all we need to do is subtract one value from the other to obtain the result of 3 hours and five minutes.
Using Dates And Times Together
Our final discussion on this topic focuses on using dates and times together. With what you’ve already learned about dates and times, you are probably not surprised to learn that you can use dates and times in tandem in your calculations. For example, suppose a truck left your loading dock at 6:00 a.m. on September 6th and arrived at its destination at 9:00 p.m. on September 8th. Further, suppose you need to know the exact amount of elapsed time between when the truck left and when it arrived.
You can calculate the elapsed time by simply subtracting the departure time from the arrival time, as shown below in Figure 2.
In the example provided above, the Arrival Time’s value is 45908.875. Further, the Departure Time’s value is 45906.250. Of course, both values were formatted to a date/time format so that they appear as dates/times. If we wanted or needed to know the exact number of hours that elapsed, we could multiply the elapsed time expressed in days (2.625) by the number of hours in a day (24) to arrive at a total number of elapsed hours. That value is 63 hours.
Summary
Many Excel users – even those with decades of experience – are often perplexed with date and time calculations in the ubiquitous spreadsheet application. Yet, dates and times are quite simple. Specifically, they are simply numbers that are formatted to look like dates and times. For those who understand that structure, date-and-time-based calculations can be quite easy. And powerful! Take advantage of what you’ve learned in this article to automate your accrual, deferral, amortization, depreciation, and other date and time-based calculations. You will save time and likely improve accuracy along the way.
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.
