Subtract Time In Excel

Article byWallstreetmojo Team
Edited bySheeba M
Reviewed byDheeraj Vaidya, CFA, FRM

What Is Subtract Time In Excel?

The Subtract Time in Excel is a feature to find the time difference between the two time values, i.e., the start and end times. We can perform Excel Subtract Time of time values less than 24 hours by using the ‘-’ operator. However, the time values that on subtraction exceed 24 hours/60 minutes/60 seconds are ignored by Excel. In such scenarios, we use the Custom Number Format.

For example, we have the start and end examination time for a particular student. Enter the formula =(C2-B2)*24 in cell D2, to find the time taken or the time difference.

Subtract Date in Excel - Intro

The output is 3 hours, as shown above.

Key Takeaways

  • The Subtract Time in Excel considers two valid time values and helps users find the difference between them. We must always multiply the result by 24 to convert it into hours so that Excel doesn’t ignore even the seconds if the time difference exceeds 24 hours.
  • The end time must always be larger than the start time and the values must be positive values in custom time formats.
  • In order to get the complete number of hours after subtraction of time values, we can use the ‘INT’ function and round it off to the nearest integer.
Example 3-5
  • If the Finish Time is lesser or earlier than the Start Time, then the time difference is displayed as a negative number.
Example 3-6

Download Template

This article must help understand Subtract Time in Excel with its formulas and examples. You can download the template here to use it instantly.

You can download this Subtract Time Excel Template here – Subtract Time Excel Template

How To Subtract Time In Excel?

We do not have an inbuilt function to Subtract Time in Excel. However, we can construct some formulas and find the time difference as follows:

  1. Ensure we have a minimum of two values, start and end time.
  2. Type =(End time – Start Time)*24, as cell reference or cell values.
  3. Press the “Enter” key to calculate the time difference.

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Examples

We will consider examples to Subtract Time in Excel.

Example #1

In the given dataset, we have the Start and Finish Time of doing an assignment by some studentsin cells B2, B3, B4, and C2, C3, C4, respectively. Let us calculate the total time taken by these students to complete the assignment in cells D2, D3, and D4.

Subtract time Example 1

The steps to calculate the time difference are as follows:

  1. So, we subtract the two given time values using the “–” operator and then multiply the result by 24 to get the number of hours taken to complete the assignment. This works quite easily as the subtraction value of given times is no more than 24.


    Subtract time Example 1-1

  2. We get the result as 3.00.


    Subtract time Example 1-2

  3. Drag the formula from C2 to C4.


    Subtract time Example 1-3

Example #2

Now, let us Subtract Time in Excel that is more than 24 hours when the start time and finish time of doing the assignment are given:

ST Example 2

We can see that the Start and Finish Time taken by three students to complete an assignment are provided in cells B2, B3, B4, and C2, C3, and C4, respectively, and we wish to calculate the total time (in hours) taken by these students to complete the assignment in cells D2, D3, and D4.

Subtract time Example 2-1

We get the result as given below:

Subtract time Example 2-2

These DateTime values (that cross midnight) are written using custom formats that can be used to display time intervals that exceed the standard time units’ length.

The following steps illustrate how to apply the custom number format in excelCustom Number Format In ExcelExcel custom number formatting is nothing but making the data look better or visually appealing. Excel has many inbuilt number formatting. On top of this, we can customize the Excel number formatting by changing the format of the numbers.read more:

  • Click the ‘Home’ tab and expand the ‘Number Format’ drop-down. Click on the ‘More Number Formats…’ option, as shown below.
ST Example 2-3
  • Select ‘Custom’, then in the ‘Type’ box, select: ‘dd-mm-yyyy hh:mm AM/PM’ and click ‘OK.’
St Example 2-4

The next time we need this format, it will be saved in the ‘Type’ list.

Thereafter, we subtract the two given time values using the ‘- operator and then multiply the resultant value by 24 to get the number of hours taken to complete the assignment.

Similarly, if we wish to calculate minutes or seconds between two-time values, then we subtract the two-time values and multiply the resultant value with 1440 or 86400 (instead of 24), respectively.

Example #3

Let’s say we wish to subtract the desired time interval from a given time. This can be done by dividing the number of hours, minutes, or seconds by the number of the corresponding unit in one day (24 hours, 1440 minutes, 86400 seconds) and then subtracting the result from the given time:

#Case 1 – Time to be subtracted under 24 hours:

Example 3

We can see that Given Time is present in cells A2 and B2, and contains a number of hours we wish to subtract from this. So, these two-time values are subtracted as follows:

Time Taken = Given Time - ( No of hours to be subtracted/24)

The result can also be achieved using the TIME() function as follows: So we can see that when we subtract 4 hours from the given time: 16:00, we get the result as 12:00.

Subtract time Example 3-1
Time Taken = Given T000ime - TIME ( No of hours to be subtracted,0,0)

However, when we wish to subtract under 24 hours, only then the TIME() function can be used. So, we see that the TIME() function also gives the same result.

Subtract time Example 3-2

#Case 2 – Time to be subtracted above 24 hours:

Subtract time Example 3-3

We can see that Given DateTime is present in cell A4, and cell B4 contains a number of hours we wish to subtract from this. Now, these two-time values are subtracted as follows:

Time Taken = Given Time - ( No of hours to be subtracted/24)

#Case 3 – Time to be subtracted above 60 minutes or 60 seconds: Hence, we find that this formula has no limitations to the number of hours we desire to subtract. So, when 26 hours (>24) are subtracted from the Given DateTime: ‘27-03-2019 15:56’, we get ‘26-03-2019 13:56’ as a result.

Subtract time Example 3-4

Here, the two-time values are subtracted by using the same formula as above. The only difference in the formula would be that:

  • The number of minutes that we desire to subtract from the given DateTime are divided by 1440 (when the time to be subtracted is above 60 minutes, as 1 day has 1440 minutes), and the formula would work as follows:
Time Taken = Given Time - ( No of minutes to be subtracted/1440)
  • The number of seconds that we desire to subtract from the given DateTime is divided by 86400 (when the time to be subtracted is above 60 seconds as 1 day has 86400 seconds), and the formula would work as follows:
Time Taken = Given Time - ( No of seconds to be subtracted/86400)

Important Things To Note

  • We can add hours/minutes/seconds to a DateTime in Excel using custom number formats or custom number formats that can be used to format time values that are beyond 24 hours, or 60 minutes, or 60 seconds.
  • The Custom time formats work only for positive time values.
  • If, after applying the custom formatting, a cell displays a sequence of ‘#####,’ then this is due to the fact that the width of the cell is not sufficient enough to display the date-time value.

Frequently Asked Questions

1. How to open the “Format Cells” window to custom the Time and Date format?

There are various methods to open the Format Cells window, namely,
Method 1 Simply press the shortcut key “Ctrl+1”.
Method 2 Select the “Home” tab – go to the “Number” group – click the “Number Format” option drop-down – select the last option “More Number Formats…”, as shown below.

Custom Number Format in Excel - More Number Format

Method 3 Right-click on any cell, and select the “Format Cells…” option from the list, as shown below.

Format Cell

Method 4 Select the “Home” tab – go to the “Number” group – click the “Number Format” box, i.e., the small box at the bottom right of the “Number” group, as shown below.

Number Format

2. Where is the TIME function in Excel found?

The TIME function in Excel is found as follows:
First, choose an empty cell – select the “Formulas” tab – go to the “Function Library” group – click the “Date & Time” option drop-down – select the “TIME” function, as shown below.

Subtract Date in Excel - FAQ 2 - Time

3. Where is the INT function found to round off the Excel Subtract Time difference?

The INT function in Excel is found as follows:
First, choose an output or result cell – select the “Formulas” tab – go to the “Function Library” group – click the “Math & Trig” option drop-down – select the “INT” function, as shown below.

Subtract Date in Excel - FAQ 3 - INT

Recommended Articles

This has been a guide to Subtract Time in Excel. Here we Subtract Time by building formulas, Format Cells, TIME, INT, examples & downloadable excel template. You may learn more about excel from the following articles –