Pinterest Pixel

Calculate Elapsed Time in Excel

  What does it do? Converts a formula to text and lets you specify the display formatting... read more

Download Excel Workbook
John Michaloudis
Posted on

Overview

Calculate Elapsed Time in Excel | MyExcelOnline Calculate Elapsed Time in Excel | MyExcelOnline

 

What does it do?

Converts a formula to text and lets you specify the display formatting by using special format strings

Formula breakdown:

=TEXT(value1 – value2, format text)

What it means:

=TEXT(formula, a text string enclosed in quotation marks)


When you have two points in time and you want to calculate the amount of time elapsed, then you will need to use Excel´s TEXT function

Sometimes data gets dumped into Excel with the following date & time format:

24/01/2015  19:48:00.

Using the TEXT function and entering a special text string can give you the time elapsed in Days, Hours, Minutes and Hours & Minutes.

See below how easy this is to implement.

Calculate Elapsed Time in Excel | MyExcelOnline

Download excel workbookText-Elapsed-Time.xlsx

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”)

value1 is the end date time
value2 is the start date time
format text is “dd” which signifies days

See also  Sum a Range Using the INDEX Function

Calculate Elapsed Time in Excel

 

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

Calculate Elapsed Time in Excel

 

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

Calculate Elapsed Time in Excel

 

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

Calculate Elapsed Time in Excel

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

Calculate Elapsed Time in Excel

 

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!

See also  IF Function Combined With The AND Function

Calculate Elapsed Time in Excel

How to Use the Text Formula in Excel

 

 

 

If you like this Excel tip, please share it
Calculate Elapsed Time in Excel | MyExcelOnline Calculate Elapsed Time in Excel | MyExcelOnline
Founder & Chief Inspirational Officer at MyExcelOnline.com

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...