# 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.

• 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.

Read More: How to Subtract Hours from Time in Excel

## 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.

Read More: How to Subtract Military Time in Excel

## 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.

## Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Lutful Hamid

LUTFUL HAMID is an outstanding marine engineer who finds joy in navigating the realms of Excel and diving into VBA programming. To him, programming is like saving time when dealing with data, files, and the internet. His skills extend beyond the basics, covering Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA. Armed with a B.Sc in Naval Architecture & Marine Engineering from BUET, he's shifted gears and now serves as a content developer. In this role,... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF