How to Subtract Date and Time in Excel - 6 Methods

The datasheet contains start and end time. To calculate the duration of each project:

Method 1 – Getting Days, Hours, and Minutes between Dates by Combining TEXT and INT Function

Use the INT and the TEXT functions.

STEPS:

Enter the formula in E5.

=INT(D5-C5)&" days "&TEXT(D5-C5,"h"" hrs ""m"" mins """)

Formula Breakdown

(D5-C5) >> returns the value of the subtraction of D5 and C5.
Output is >> 9.99943969907326

INT(D5-C5) >> returns the integer portion of the previous result.
Output is >> 9
Explanation >> Integer portion of (D5-C5)

INT(D5-C5)&" days " >> Joins 9 and the text days
Output is >> 9 days
Explanation >> Concatenated part of 9 days

TEXT(D5-C5,"h"" hrs ""m"" mins """) >> Converts the result C5-D5 into hours and minutes and adds the text hrs, mins.
Output is >> “23 hrs 59 mins ”
Explanation: The TEXT function converts the text into hours and minutes. The text hours and minutes is added to the values.

INT(D5-C5)&" days "&TEXT(D5-C5,"h"" hrs ""m"" mins """) >> returns the value of subtraction of total days, hours, and minutes.
Output is >> 9 days 23 hrs 59 mins
Explanation >> The difference between two given dates and times.

Press Enter to see the result.

Drag down the Fill Handle to see the result in the rest of the cells.

Method 2 – Using the Excel TIME Function with the Hour, and Minute Functions to Subtract Date and Time

Use the TIME function, the HOUR and the MINUTE functions.

STEPS:

Enter the formula in E5.

=TIME(HOUR(C5),MINUTE(C5),0)-TIME(HOUR(D5),MINUTE(D5),0)

Formula Breakdown

HOUR(C5)>> returns the hour value of C5.
Output is>>22
Explanation>> Hour value of 22:59

MINUTE(C5)>>returns the minute value of C5.
Output is>>59
Explanation>> Minute value of 22:59

TIME(HOUR(C5),MINUTE(C5),0)>> returns the numerical value of hour, minute, and seconds.
Output is>>0.957638888888889
Explanation>> converts the numerical value of 22 hours and 59 minutes

TIME(HOUR(C5),MINUTE(C5),0)-TIME(HOUR(D5),MINUTE(D5),0)>>
Output is>> 0.41875
Explanation>> Numerical value of subtraction of two values.

To format this numerical value, go to Number.

$C:\Users\Hp\Pictures\9\Subtract time and date 2.png$

In the dialog box, choose Custom and enter h:mm:ss. (You can select other format).

Press Enter to see the result.

Drag down the Fill Handle to see the result in the rest of the cells.

Method 3 – Calculating Time Difference with the Excel TEXT Function

STEPS:

Enter the formula in E5.

=TEXT(D5-C5,"h""Hours""m""Mins""")

Formula Breakdown

D5-C5>> returns the value of the subtraction of D5 and C5
Output is>> 0.375

"h""Hours""m""Mins""">> is the format to express the value: hours followed by the word Hours, minutes followed by the word Mins.

TEXT(D5-C5,"h""Hours""m""Mins""")>>
Output>> 9Hours0Mins
Explanation>> returnes a value in hours and minutes format.

Press Enter to see the result.

Drag down the Fill Handle to see the result in the rest of the cells.

Method 4 – Calculating Elapsed Time Using the Excel NOW/ TODAY Functions

Use the NOW and the TODAY functions.

STEPS:

Enter the formula in E5.

=TEXT(NOW()-C5,"d""days""h""hours""m""mins""s""secs""")

Formula Breakdown

NOW()-C5>> returns the difference between the current time and the given time.
Output is>> 10.1800935185165
Explanation>> The difference between the current time and the given time.

"d""days""h""hours""m""mins""s""secs""">> the format to convert the output: days followed by the word days, hours followed by the word hours, and so on.

TEXT(NOW()-C5,"d""days""h""hours""m""mins""s""secs""")>> returns the difference in a specified format.
Output>>10days4hours20mins10secs
Explanation>> The difference is expressed in the specified format.

Press Enter to see the result.

Drag down the Fill Handle to see the result in the rest of the cells.

Method 5 – Subtracting a Specified Amount of Time from a Given Time

STEPS:

Enter the formula in E5.

=C5-TIME(0,D5,0)

Formula Breakdown

TIME(0,D5,0)>> returns the numerical value of the given period of time in D5
Output is>> 0.0208333333333333
Explanation>> 30 minutes are converted into a numerical value

C5-TIME(0,D5,0)>> returns the date and time by subtracting the given period of time.
Output is>> 44606.5182572917
Explanation>> Numerical value of the resultant time.

Press Enter to see the result.
Format the result using the process shown in Method 2.

Drag down the Fill Handle to see the result in the rest of the cells.

Method 6 – Using the Minus (-) Sign and the TEXT Function to Subtract Date and Time in Excel

STEPS:

Enter the formula in E5.

=TEXT(D5-C5,"hh:mm:ss")

Formula Breakdown

D5-C5>> returns the difference between the Start date and the End date.
Output is>> 9.99943969907326
Explanation>> The numerical value of the difference of the two given times.

"hh:mm:ss">> Specifies the format of the output: Hour: Minute: Second

TEXT(D5-C5,"hh:mm:ss")>> returns the resultant value in a specified format
Output>> 23:59:12
Explanation>> 9.99943969907326 expressed in the specified format.

Press Enter to see the result.

Drag down the Fill Handle to see the result in the rest of the cells.

Things to Remember

To choose the correct date format, follow the steps shown in method 2.

Download Practice Workbook

Difference in Date and Time.xlsx

<< Go Back to Subtract Time | Calculate Time | Date-Time in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

How to Subtract Date and Time in Excel – 6 Methods

Method 1 – Getting Days, Hours, and Minutes between Dates by Combining TEXT and INT Function

Method 2 – Using the Excel TIME Function with the Hour, and Minute Functions to Subtract Date and Time

Method 3 – Calculating Time Difference with the Excel TEXT Function

Method 4 – Calculating Elapsed Time Using the Excel NOW/ TODAY Functions

Method 5 – Subtracting a Specified Amount of Time from a Given Time

Method 6 – Using the Minus (-) Sign and the TEXT Function to Subtract Date and Time in Excel

Things to Remember

Related Articles

Leave a reply Cancel reply