Calculate Elapsed Time in Excel

Have you ever stared at a spreadsheet filled with timestamps and thought, “There’s got to be an easier way to figure out how much time passed between these two events”? I’ve been there more times than I can count—especially when analyzing log files, tracking project durations, or even figuring out how long my internet was down (yes, I track that too).

Let me walk you through how I calculate elapsed time in Excel, using one of my all-time favorite tricks—the TEXT function. It’s simple, versatile, and honestly a lifesaver when you need to break down elapsed time into days, hours, minutes, or any combination.

Key Takeaways:

• The TEXT function converts time differences into easy-to-read formats using custom text strings.
• Elapsed time can be shown as days, hours, or minutes.
• Raw subtraction of timestamps works, but TEXT makes the results human-friendly.
• Formats like “[hh]” ensure the count doesn’t reset every 24 hours—super useful!
• Once your formula is set, just drag it down to calculate time differences across your dataset.

Power of the TEXT Function for Time Calculations

What does it do?

The TEXT function is like Excel’s personal stylist—it converts a formula result into text and lets you apply formatting just the way you want. The cool part? You can take a time difference and display it however it makes the most sense for your use case.

Here’s the formula we’ll be working with:

=TEXT(value1 – value2, format_text)

What it means:

• value1: Your end time (later date & time)
• value2: Your start time (earlier date & time)
• format_text: A string in quotation marks that defines how you want the elapsed time to appear (e.g., “dd” for days, “[hh]” for hours)

Why This Matters

Sometimes when data gets dumped into Excel (I’m looking at you, CRM exports and server logs), the format looks like this:

24/01/2015 19:48:00

What I used to do: squint at two timestamps and mentally subtract them.
What I do now: let Excel do the heavy lifting using TEXT.

This function allows me to easily convert time differences into readable, clean output—whether I need to show just days, hours, minutes, or even a combined format like [h]:mm.

Let me show you just how easy this is to implement.

STEP 1: Enter the following to get the elapsed time in days:

We need to enter the TEXT function in a blank cell:

=TEXT(B12-A12, "dd")

• value 1 is the end date time
• value2 is the start date time
• format text is “dd” which signifies days

STEP 2: Apply the same formula to the rest of the cells by dragging the lower right corner downwards.

STEP 3: Enter the following to get the elapsed time in hours:

We need to enter the TEXT function in a blank cell:

=TEXT(B12-A12, "[hh]")

• value1 is the end date time
• value2 is the start date time
• format text is “[hh]” which signifies hours

STEP 4: Apply the same formula to the rest of the cells by dragging the lower right corner downwards.

STEP 5: Enter the following to get the elapsed time in hours and minutes:

We need to enter the TEXT function in a blank cell:

=TEXT(B12-A12, "[h]:mm")

• value1 is the end date time
• value2 is the start date time
• format text is “[h]:mm” which signifies hours and minutes

STEP 6: Apply the same formula to the rest of the cells by dragging the lower right corner downwards. And your elapsed time results are all ready!

 

Real-World Examples

• Customer Service Analysis – I once worked on a dashboard tracking how long customer issues took to resolve. The raw data had “opened at” and “closed at” timestamps. With a quick TEXT formula, I could instantly report average resolution times.
• Event Tracking for Web Logs – When analyzing user session times, I needed to know how long users stayed active on a site. Again, TEXT(end – start, “[h]:mm”) gave me total session durations down to the minute.
• Monitoring Task Durations – I used it in project management sheets to see how long tasks took from kickoff to completion. Clients love when you show this kind of data—it adds trust and transparency.

 

FAQs

1. Why should I use the TEXT function instead of just subtracting times directly?

When you subtract two timestamps in Excel, it gives you a decimal value that represents the time difference in days. While accurate, it’s not very readable. The TEXT function formats the result into a clear and user-friendly display—like “48 hours” instead of “2”. It also gives you control over how much detail to show: days, hours, minutes, or any combo.

2. What do the brackets around [hh] or [h]:mm actually do?

Brackets tell Excel not to reset the count at the 24-hour mark. Without them, hh would only show 0–23, which works for clocks, not durations. With brackets, [hh] gives you the total number of hours, even if it’s 200+. This makes it ideal for calculating long durations like server uptime, project timelines, or travel time.

3. Can I use TEXT to calculate time differences across multiple days or weeks?

Yes! That’s where the TEXT function shines. Using formats like “dd” or “[h]:mm”, you can span any time period. For example, a difference of 10 days and 4 hours can be shown as 10 (days), or 244:00 (hours), depending on the format string. You just need to decide how you want the data to be presented.

4. Does the TEXT function affect further calculations or formulas?

Yes, and that’s a crucial detail. The TEXT function outputs a text string, not a number. That means you can’t do math with it unless you use the raw subtraction in a separate column. Best practice: keep the time difference formula (=B12 – A12) in one column and the TEXT() version in another for display.

5. What kind of formatting strings can I use with the TEXT function?

There are several powerful format strings you can apply:

• “dd” for full days
• “[hh]” for total hours
• “mm” for minutes (be careful—this also means months in some cases!)
• “[h]:mm” for total hours and minutes

Play around with combinations based on how much detail you need to show.