Excel duration calculation is a fundamental skill for project managers, finance analysts, and operations teams. Tracking the difference between two dates or times reveals how long a task actually takes, exposing bottlenecks and inefficiencies hidden in daily workflows.
Understanding the Basics of Time Arithmetic
At its core, Excel stores dates and times as serial numbers. January 1, 1900, is represented as the number 1, and each subsequent day increments by 1. Because of this architecture, subtracting one date from another does not yield text like "2 days"; it returns a number representing the total days between them. To display this result correctly, you must apply a custom time format. Selecting the cell and choosing [h]:mm:ss ensures the duration rolls over correctly past 24 hours, rather than resetting to zero.
Calculating Elapsed Hours and Days
When you need to determine the total hours between a start and end time, the math is straightforward. You multiply the raw difference by 24. For example, the formula =(B2-A2)*24 returns a decimal representing total hours, including fractions. If you prefer to see only whole hours, wrapping the result in the INT or ROUND functions cleans up the output. For longer projects measured in days, a simple end minus start provides the exact count, which is useful for calculating project velocity or employee tenure.
Handling Negative Durations
A common pitfall in Excel duration calculation occurs when the end time is earlier than the start time, resulting in a negative value. Standard date formatting will often display this as a string of hash characters or an incorrect date. To resolve this, navigate to the File menu, select Options, and navigate to the Advanced section. Enabling the 1904 date system is one solution, but a more robust method is to wrap your subtraction in an IF statement. Using =IF(B2>=A2, B2-A2, (1-B2)+A2) allows the logic to account for crossing the midnight threshold or missing data, ensuring the calculation always returns a logical result.
Working with the TEXT Function
For presentation purposes, the TEXT function is indispensable. While raw numbers are accurate, they are not always human-readable. By converting the serial number into a string, you can dictate exactly how the duration appears. To display hours over 24 correctly, use the format code "[h]" inside the function, as in =TEXT(B2-A2, "[h]") . This specific format ignores the 24-hour cycle and sums the total hours, which is essential for logging billable hours or machine runtime without the date component interfering.
Summing Durations Across Multiple Rows
Projects rarely consist of a single task; they are a series of intervals. To find the total time spent, you must sum the individual durations. The SUM function usually works, but users sometimes encounter errors if the columns are formatted as general text. Ensure the duration column is formatted as time before summing. A reliable approach is to use =SUM(C2:C10) where column C contains the calculated differences. For a more dynamic view, combining this with a FILTER or SUMIFS allows you to isolate durations based on specific criteria, such as department or project phase.
