How to Subtract Date and Time in Excel?

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.

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.

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.

2. Using Excel TIME Function with Hour, and Minute Functions to Subtract 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.

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

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.

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.

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.

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

6. Using Minus (-) Sign & TEXT Function to Subtract 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.

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

Difference in Date and Time.xlsx

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.

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

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

2. Using Excel TIME Function with Hour, and Minute Functions to Subtract Date and Time

3. Calculating Time Difference with Excel TEXT Function

4. Calculating Elapsed Time Using Excel NOW/ TODAY Function

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

6. Using Minus (-) Sign & TEXT Function to Subtract Date and Time in Excel

Things to Remember

Conclusion

Related Articles

Leave a reply Cancel reply