How to Subtract Date and Time in Excel (6 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

How to Subtract Date and Time in Excel

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

How to Subtract Date and Time in Excel

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.

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.

How to Subtract Date and Time in Excel

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""")
How to Subtract Date and Time in Excel

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


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.

How to Subtract Date and Time in Excel

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

How to Subtract Date and Time in Excel

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)

How to Subtract Date and Time in Excel

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")

How to Subtract Date and Time in Excel

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.

How to Subtract Date and Time in Excel

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

Lutful Hamid
Lutful Hamid

Hello! Welcome to my Profile. I have completed BSc in Naval Architecture and Marine Engineering from BUET and MBA from IBA. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo