Calculating Time Difference with the TEXT Function

How to calculate time difference with the text function in Excel sheets?

Text Formula to Subtract Time

A simple tutorial to calculating time difference with the TEXT function. Here the formula are in the text format. There are three formulas to calculate time.

Formula

=TEXT(End time-Start time, "h") To calculate hours between two times
=TEXT(End time-Start time, "h:mm")Return hours and minutes between 2 times
=TEXT(End time-Start time, "h:mm:ss")Return hours, minutes and seconds between 2 times

Example:
Lets perform each formula in excel to get a clear picture
Formula 1
Calculate hours between two times
=TEXT(B1-A1,"h")
Step 1:
Enter the two time values in separate cell to find the difference.
Hours Between Two Times Step1
Step 2: To find the hour difference, enter =(equal to symbol), open parenthesis, select the cell where your entered the starting value, followed by -(minus symbol) and now select the cell containing the end time, close the parenthesis. Put a comma symbol and enter as "h"
Hours Between Two Times Step2
Step 3:
Hit the enter button to know the hour difference left between the two time.
Hours Between Two Times Step3

Formula 2
Calculate hours and minutes between 2 times
=TEXT(B2-A2, "h:mm")
Step 1: Enter the two values in separate cells.
To find the hour and minute difference, enter =(equal to symbol), open parenthesis, select the cell where your entered the starting value, followed by -(minus symbol) and now select the cell containing the end time, close the parenthesis. Put a comma symbol and enter as "h:mm"
Hours Minutes Between Two Times Step1
Step 2:
Click on the enter button to find the hour and minute difference between the two entered values.
Hours Minutes Between Two Times Step2
Formula 3
Find hours, minutes and seconds between 2 times

=TEXT(B2-A2, "h:mm:ss")
Step 1: Enter two times in separate cell in the format hour, minute and second, separating each with :(colon) symbol.
Hours Minutes Seconds Between Two Times Step1
Step 2:
To find the hour and minute difference, enter =(equal to symbol), open parenthesis, select the cell where your entered the starting value, followed by -(minus symbol) and now select the cell containing the end time, close the parenthesis. Put a comma symbol and enter as "h:mm:ss"
Hours Minutes Seconds Between Two Times Step2
Step 3:
Hit the enter button to know the time difference between hours, minutes and seconds.
Hours Minutes Seconds Between Two Times Step3


Related Topics