Very often we need to subtract two dates in our day-to-day operation. When we calculate age or duration, typically, we subtract two dates. In this article, we will show you how to subtract date and time in Excel. We will walk you through 6 easy ways using which you will be able to subtract dates in Excel easily.

Suppose we have a datasheet containing the starting time and end time. We will calculate the duration of each of the projects using different functions in Excel. In the datasheet, we have 4 columns. We have the Name of the Project, Start, and End columns. We will calculate the duration in the Duration column using different functions in Excel.

We will now guide you step by step on how can you **subtract** date and time in Excel. We will do so using 6 easy ways.

**Table of Contents**Expand

## 1. Getting Days, Hours, and Minutes between Dates by Combining TEXT and INT Function

We can use the **INT **and **TEXT** functions of Excel to subtract dates and times in Excel. **INT **function returns the integer value of a number while the **TEXT **function is used to convert any numeric value into a specific format. We can use both of these in conjunction to subtract date and time in Excel. We will use the Ampersand symbol (&) to concatenate the texts.

**STEPS**:

- To do so, in
**cell****E5**we type,

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

**Formula Breakdown**

** (D5-C5)** >> Gives the value of subtraction of

**cells D5**and

**C5**.

**Output**

**is**>> 9.99943969907326

** INT(D5-C5)** >> Gives us the integer portion of the result of subtraction of

**cells C5**and

**D5**.

**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 of

**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 are added to the values.

** INT(D5-C5)&" days "&TEXT(D5-C5,"h"" hrs ""m"" mins """)** >> Gives us 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.

**Enter**key we will get the result in the**E5 cell**.

- Now by dragging down or double-clicking the
**Fill Handle**to use the**AutoFill**feature of Excel, we will get values in corresponding cells.

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

## 2. Using Excel TIME Function with Hour, and Minute Functions to S**ubtract** Date and Time

The **TIME** function of Excel converts any time value to a numerical value. The **HOUR **function and **MINUTE** function of Excel returns the hours and minutes of a given time value. We can use the **TIME** function with the **HOUR** and **MINUTE** function to get the time difference in Excel.

**STEPS**:

- To do so, in
**cell E5**we type,

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

** **

**Formula Breakdown**

** HOUR(C5)**>> Gives us the hour value of the

**C5 cell**.

**Output is**>>22

**Explanation**>> Hour value of 22:59

** MINUTE(C5)**>>Gives us the minute value of the

**C5 cell**.

**Output is**>>59

**Explanation**>> Minute value of 22:59

** TIME(HOUR(C5),MINUTE(C5),0)**>> Returns the numerical value of hour, minute, and seconds are given. Here, we do not put any second value.

**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 of the cells.

- For formatting this numerical value we need to go to the
**Number**menu. A new**dialogue box**will pop up.

- Then go to
**Custom**and type**h:mm:ss**. We can also type other**formats**if we want.

- Pressing the
**Enter**key we will get the result in the**E5 cell**.

- Now by dragging down or double-clicking the
**Fill Handle**to use the**AutoFill**feature of Excel we will get values in corresponding cells.

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

## 3. Calculating Time Difference with Excel TEXT Function

**TEXT **function in Excel is used to convert any numeric value into a specific format. We can express the difference in time using the **TEXT** function. To do so, we will find the difference, and using the text function we will convert the difference into an **hour : minute: second** format.

**STEPS**:

- To do so, in
**cell E5**, we type

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

**Formula Breakdown**

** D5-C5**>> Returns the value of subtraction of

**cells D5**and

**C5**

**Output is**>> 0.375

** "h""Hours""m""Mins"""**>> It is the format in which we want to express our value. We will get our output in hours followed by the word

**Hours**, minutes followed by the word

**Mins**.

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

**Output**>> 9Hours0Mins

**Explanation**>> Returned value in hours and minutes format.

- Pressing the
**Enter**key we will get the result in the**E5 cell**.

- Now by dragging down or double-clicking the
**Fill Handle**to use the**AutoFill**feature of Excel we will get values in corresponding cells.

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

## 4. Calculating Elapsed Time Using Excel NOW/ TODAY Function

The **NOW** function of Excel returns the value of the current time. The **TODAY** function returns the current date. If we are to get the time elapsed between now and a given date, we can use the **NOW** or **TODAY** function to get that. We can convert the value using the **TEXT** function. **TEXT **function in Excel is used to convert any numeric value into a specific format.

**STEPS**:

- To do so, in
**cell D5**, we type,

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

**Formula Breakdown**

** NOW()-C5**>> Gives us 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"""**>> It is the format in which we want to convert our output. We will get our results in 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""")**>> Gives us the difference in a specified format.

**Output**>>10days4hours20mins10secs

**Explanation**>> The difference is expressed in the specified format.

- Pressing the
**Enter**key we will get the result in the**D5 cell**.

- Now by dragging down or double-clicking the
**Fill Handle**to use the**AutoFill**feature of Excel we will get values in corresponding cells.

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

## 5. Subtracting a Specified Amount of Time from a Given Time

We can subtract a specified amount of time from a given time. We can use this using the **TIME **function of Excel. Suppose we are to subtract a specified amount of time in the Difference(Mins) column from the Time column and get the values in the Result column.

**STEPS**:

- To do so, we will type the following formula in the
**E5 cell**.

`=C5-TIME(0,D5,0)`

**Formula Breakdown**

** TIME(0,D5,0)**>> Gives us the numerical value of the given period of time in

**cell D5**

Output is>> 0.0208333333333333

Output is

**Explanation**>> 30 minutes converted into numerical value

** C5-TIME(0,D5,0)**>> Gives us the date and time by subtracting the given period of time.

**Output is**>> 44606.5182572917

**Explanation**>> Numerical value of the resultant time.

We will need to format the result using the process shown in **method 2**.

- Pressing the
**Enter**key we will get the result in the**E5 cell**.

- Now by dragging down or double-clicking the
**Fill Handle**to use the**AutoFill**feature of Excel we will get values in corresponding cells.

**Read More: **How to Subtract Military Time in Excel

## 6. Using Minus (-) Sign & TEXT Function to S**ubtract** Date and Time in Excel

As shown in** method 3** we can use, the minus sign (-) to determine the difference between two dates coupled with the **TEXT** function.

**STEPS**:

- To do so, in
**cell E5**we type,

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

**Formula Breakdown**

** D5-C5**>> Gives us the difference between the Start date and the End date.

**Output is**>> 9.99943969907326

**Explanation**>> The numerical value of the difference of two given times.

** "hh:mm:ss"**>> Specifies the format of the output. Our output will be in

**Hour: Minute: Second**format.

** TEXT(D5-C5,"hh:mm:ss")**>> Gives us the resultant value in a specified format

**Output**>> 23:59:12

**Explanation**>> 9.99943969907326 expressed in the specified format.

- Pressing the
**Enter**key we will get the result in the**E5 cell**.

- Now by dragging down or double-clicking the
**Fill Handle**to use the**AutoFill**feature of Excel we will get values in corresponding cells.

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

## Things to Remember

When you are dealing with any date or time value make sure it is in the correct format. Otherwise, you may not get the desired result. To choose the correct format follow the steps shown in **method 2**.

**Download Practice Workbook**

## Conclusion

In this article, we have tried to cover how to subtract date and time in Excel. If you have any suggestions or feedback you can leave a comment below or reach out to us. For any of your Excel-related queries please have a look at our website. Our team would be happy to help you out.

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