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 S**ubtract** 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.

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

**Read More: **How to Calculate Difference Between Two Times in Excel

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

**Read More: **How to Subtract Minutes from Time in Excel

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

Output is

**Â Â Â Â Â Â 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 S**ubtract** 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.

**Read More: **How to Calculate Time Difference in Minutes in Excel

## Things to Remember

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

**Download Practice Workbook**

**Related Articles**

- How to Subtract and Display Negative Time in Excel
- How to Calculate Time Difference in Numbers
- [Fixed!] VALUE Error (#VALUE!) When Subtracting Time in Excel
- How to Subtract Time and Convert to Number in Excel

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