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

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

We can use the **INT **and **TEXT** function of Excel to** subtract** date and time 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**and

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

**hrs**,

**mins**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 Calculate Total Hours in Excel (9 Easy Methods)**

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

**TIME** function of Excel converts any time value to 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 if 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 Time in Excel (7 Quick Methods)**

## 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 Dates and Times in Excel**

**Similar Readings**

**How to Calculate Turnaround Time in Excel (4 Ways)****Calculate Hourly Rate in Excel (2 Quick Methods)****How to Use Time Format in Excel VBA (Macro, UDF, and UserForm)****Calculate the Duration of Time in Excel (7 Methods)**

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

**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 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 result 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 Calculate Elapsed Time in Excel (8 Ways)**

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

**Related Content:**** How to Subtract Military Time in Excel (3 Methods)**

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

As shown in** method 3** we can use, 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

**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 Subtract and Display Negative Time in Excel (3 Methods)**

## 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 Add Time in Excel Over 24 Hours (4 ways)****Excel Formula To Calculate Time Worked****How to Add Hours to Time in Excel (8 Quick Ways)****Calculate Average Response Time in Excel (4 Methods)****Excel Formula for Overtime over 8 Hours (4 Examples)****Excel Formula to Calculate Hours Worked Minus Lunch****How to Subtract Hours from Time in Excel (2 Easy Ways)**