Lutful Hamid
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 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.
- 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 Total Hours in Excel (9 Easy Methods)
2. Using Excel TIME Function with Hour, and Minute Functions to Subtract 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
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 Subtract 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)
