If the start timestamp is in A2 and the end timestamp is in B2, enter =B2-A2 to calculate the elapsed time. Format the result as [h]:mm:ss to show total hours, minutes, and seconds, including durations longer than one day.
How Excel Calculates Time Between Timestamps
Excel stores dates and times as numbers. In the standard date system, a whole-number part represents a date and the decimal part represents a fraction of a 24-hour day. For example, 12:00 noon is one-half of a day. Subtracting one valid timestamp from another therefore gives a duration measured in days, even though Excel may display that result as a clock time.
For instance, if A2 contains 9:15 AM and B2 contains 5:45 PM on the same date, the difference is 8 hours and 30 minutes. The result underneath is 0.3541667 days before formatting, because 8.5 hours is 8.5 divided by 24. The underlying number is useful: it lets you add durations, average them, or convert them to hours or minutes.
The subtraction method works best when both cells contain real Excel date-and-time values, not text that merely looks like a timestamp. A timestamp can be entered as a date and time in one cell, such as 11/2/2026 9:15 AM, or assembled from separate date and time columns. The values must use the same date system and represent comparable points in time.
Format the Result as an Elapsed Duration
A frequent surprise is that a correct subtraction result appears as a decimal, a time of day, or a run of hash marks. The calculation and its display format are separate things. Excel’s ordinary time format is designed to show the time on a clock; after 24 hours, it can wrap around and show the remainder rather than the full elapsed duration.
For a duration that may exceed 24 hours, use the custom number format [h]:mm:ss. The square brackets tell Excel to accumulate hours instead of resetting them at each day boundary. A result of 27 hours and 15 minutes will display as 27:15:00, while the format h:mm:ss would show 3:15:00.
- Select the cells containing the calculated durations.
- In Excel for Microsoft 365 on Windows, press Ctrl+1 to open Format Cells. On Mac, press Command+1.
- Choose the Number tab, select Custom, enter
[h]:mm:ssin the Type box, and select OK.
You can use [h]:mm when seconds are not needed, or [m]:ss to show a long duration as total minutes and seconds. In Excel for the web, select the cells and use Home > Number Format > More Number Formats to set a custom format if that option is available in the current interface. Applying a format changes how a value appears, not the stored duration or the calculation.
Convert the Difference to Decimal Hours, Minutes, or Seconds
Time values are fractions of a day, so multiply a duration by the number of the relevant units in a day. This is useful when a report needs a numeric quantity, such as 8.5 hours for payroll calculations or 510 minutes for an elapsed-time summary. The result is a number, not a formatted clock display.
- With the start timestamp in A2 and end timestamp in B2, use
=(B2-A2)*24for decimal hours. The 9:15 AM to 5:45 PM example returns 8.5. - With those same timestamps, use
=(B2-A2)*1440for total minutes. The example returns 510. - With those same timestamps, use
=(B2-A2)*86400for total seconds. The example returns 30,600.
For a rounded decimal-hour result, with the same A2 and B2 layout, use =ROUND((B2-A2)*24,2). The example returns 8.5; a duration such as 8 hours, 20 minutes would return 8.33 when rounded to two decimal places. Rounding is appropriate for presentation or a defined reporting rule, but avoid rounding intermediate values if later calculations need greater precision.
Calculate a Duration Across Midnight
If the cells contain complete date-and-time timestamps, ordinary subtraction already handles midnight correctly. For example, a start at 10:00 PM on Monday and an end at 6:00 AM on Tuesday produces 8 hours, because the date portion records that the end occurred on the following day.
A different situation arises when the cells contain only clock times, with no dates. If A2 is 10:00 PM and B2 is 6:00 AM, direct subtraction is negative because Excel cannot know whether the end time belongs to the next day. If the rule is that an earlier end clock time always means the following day, use this formula with start time in A2 and end time in B2: =MOD(B2-A2,1). It returns 8 hours.
That approach assumes each interval is less than 24 hours and that an earlier end time means an overnight shift. It is not suitable for a duration that could span several days: the modulo operation wraps the result into a single day and discards complete days. For shifts, service intervals, or events that might last 24 hours or more, store full dates as well as times and subtract the complete timestamps.
Work with Separate Date and Time Columns
Sometimes a worksheet records the start date in A2 and the start time in B2, with the end date in C2 and end time in D2. Excel can combine each date and time by addition. With that exact layout, this formula calculates the elapsed duration: =(C2+D2)-(A2+B2). Format the result as [h]:mm:ss if the duration may exceed a day.
For example, if A2 is November 2, B2 is 10:00 PM, C2 is November 3, and D2 is 6:00 AM, the result is 8:00:00. Check that the date and time cells contain numeric Excel values. A text date or time may not add as expected, and date interpretation can vary with regional settings. Consistent, unambiguous data entry makes the worksheet easier to sort, calculate, and audit.
Handle Blank Cells and Check the Data
When a worksheet is still being filled in, subtracting a blank cell can produce a misleading value rather than a useful empty result. With start timestamp in A2 and end timestamp in B2, use =IF(OR(A2="",B2=""),"",B2-A2) to leave the result blank until both timestamps are present. Apply the elapsed-duration format to the result cells as usual.
Also decide how the spreadsheet should handle an end timestamp earlier than the start. If timestamps are complete and the end is earlier, the data may contain a typing error, use an unexpected date convention, or describe a correction that needs review. Do not automatically wrap every negative difference to the next day: doing so can disguise reversed or mismatched records.
- Confirm that both timestamps are actual date-and-time values. A quick clue is that changing a cell temporarily to General displays a number for a numeric date or time.
- Check that the dates are in the intended order and that the start and end columns have not been swapped.
- Look for imported text, extra spaces, inconsistent date formats, and missing date components.
- Use the same time zone and timestamp convention for both values before subtracting them.
Common Display Problems and Misconceptions
If the result shows a decimal such as 0.3541667, Excel has likely calculated a fraction of a day correctly; change the display format or multiply by 24 for hours. If the result shows 8:30 AM when you expect 32:30, use a bracketed hour format such as [h]:mm. If it displays #####, widen the column first. If the value remains hash marks, the result may be a negative time that the workbook’s date system cannot display in the selected format.
Negative durations need deliberate handling. In a standard Excel workbook using the 1900 date system, a negative time often displays as hash marks. Changing the workbook to the 1904 date system can affect date values throughout the workbook, so it is not a casual formatting fix. Instead, check whether the timestamps are reversed or whether a time-only overnight calculation is needed. If negative durations are meaningful in the report, show them as signed decimal hours or handle their display explicitly.
Another common misconception is that Excel timestamps automatically account for time zones or daylight-saving changes. Excel stores ordinary date and time values as numbers; those values do not carry a time-zone identity. If two timestamps come from different zones, convert them to the same zone before calculating. For records spanning a daylight-saving transition, define whether the desired measure is wall-clock time or actual elapsed time, and prepare the timestamps consistently for that purpose.
Choose the Right Calculation for the Job
The simplest correct calculation is usually timestamp subtraction, but the best display depends on the question being answered. Use a duration format when people need to read hours and minutes. Use decimal hours when another calculation expects a numeric hour quantity. Use total minutes or seconds when the unit is clearer for the task. Keep the underlying duration as a numeric value rather than turning it into text, so it remains available for sums, averages, and comparisons.
For a column of records, with each start timestamp in A2:A100 and each matching end timestamp in B2:B100, enter =B2-A2 in C2 and fill it down. Format C2:C100 as [h]:mm:ss or convert each result to the required numeric unit. Each row must pair the correct start and end; a perfect formula cannot detect that two records were matched incorrectly.
Excel also has date-difference tools, but they are not a substitute for subtracting complete timestamps when the required answer includes hours, minutes, or seconds. A whole-day count intentionally discards smaller units. Decide whether you want a precise elapsed interval, a count of calendar days, or a rounded business measure before choosing a formula. The result should match the question, not merely look convenient in a cell.
Frequently Asked Questions
How do I calculate hours between two times in Excel?
With the start time in A2 and end time in B2, enter =(B2-A2)*24 for decimal hours. For a clock-style result, subtract the times and format the result as [h]:mm. If the times cross midnight and have no dates, use the overnight method only when an earlier end time reliably means the next day.
Why does Excel show a decimal instead of hours and minutes?
Excel stores a duration as a fraction of a day, so General format may display a decimal. Keep the subtraction result and apply a time or elapsed-duration format such as [h]:mm:ss. The decimal is not necessarily an error; it is the underlying numeric representation.
How do I calculate time between dates that include different days?
Subtract the complete end timestamp from the complete start timestamp in the correct order: end minus start. Because the dates are included, Excel calculates across midnight and across multiple days. Use a bracketed hour format to display all elapsed hours rather than wrapping after 24.
Can Excel calculate a shift that crosses midnight?
Yes. If the start and end cells include their dates, ordinary subtraction works. If they contain only times and every earlier end time means the next day, use the modulo formula described above. Include dates for shifts that may last a full day or longer.
What should I do if the result is negative or shows hash marks?
Check the timestamp order, date values, and whether the calculation uses clock times without dates. A negative time can display as hash marks in the standard 1900 date system. Correct the underlying data or choose a suitable numeric display; do not use an overnight adjustment unless the data’s meaning supports it.
Related
- Data Validation Checks in Excel: Rules and Examples
- Data Validation Checklist for Excel: What to Check
- Conditional Formatting in Microsoft Project: What Works
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel FIND Function: How to Find Text Within a StringIn Excel, use FIND to locate text inside a string and return the position where it begins. For…
- Copy Formulas in Excel to a New Sheet Without ErrorsTo copy formulas to a new sheet in Excel, select the formula cells, press Ctrl+C, open the destination…
- Excel FLOOR Formula for Time: Round Down by MinutesTo round an Excel time down to the nearest 15 minutes, use =FLOOR.MATH(A2,TIME(0,15,0)), assuming the time is in…
- Excel Filter by Named Range: What Works and HowYes—but Excel’s standard Filter dropdown does not accept a named range as a direct filter condition. You can…
- Bing Chat Excel Formula Not Calculating? Causes & FixesIf a formula from Bing Chat is not calculating, first paste it into an Excel cell and confirm…
- IFERROR vs ISERROR: Which Excel Function Should You Use?IFERROR catches an error produced by a calculation and returns a replacement value; ISERROR checks whether a value…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.