How to Subtract Time in Excel

Learn how subtract time and calculate the time difference between dates and dates, and negative time differences in Excel.

Subtracting time from a different time can be useful if you need to determine the difference in time between two different times. For instance, if you are trying to determine the amount of amount of time required to complete an assignment, you could enter both the beginning and ending time of the task, and subtract them to calculate the total time.

Because Excel records dates and times in numbers, it is possible to subtract them using simple formulas for arithmetic, TIME function, TEXT function or TIMEVALUE functions to determine the difference in time or the elapsed time, and much more. This article you will explore a variety of ways for subtracting time in Excel and determine the differences between times and dates in Excel.

Calculate Time Difference Between Two Times

Most of the time it is necessary to determine the difference in time between two times in order to determine the time elapsed. It is possible to calculate the time between two times or two cells using the simple subtraction.

The easiest method to determine the difference in time is by subtracting the Start Time from the time at which it ends:

=End Time - Start Time

For instance, to subtract the time in A3 from the B3 time We can apply the formula below:

=B3-A3

The time A3 is the Start Time and B3 is the End Time. You can clearly see that there is five hours of time the difference between B3 and A3.

If the format of the cell that results has been set to General it will display results that are fractions or decimals. This is due to the fact that values of time are expressed in fractions of numbers in Excel.

Within the Excel Date system time is recorded in this way:

Excel’s internal system for Excel:

• 00:00:00 AM is recorded as 0.0
• The time of 06:00 AM is recorded as 0.25
• 12:00 PM is recorded as 0.5
• 18:00 PM is recorded as 0.75
• 23:59:59 is recorded as 0.99999

If you type in the date and time in the cell the data will be saved as a serial number made up from an integer as well as a decimal. The integer is used to represent day or date and the number following the decimal point is a representation of the time. For example, if you write ‘August 15 2001, 6:00 am’ within an Excel cell it will be saved in the Excel spreadsheet as ‘37118.25’.

Thus, the 5 hours will appear as ‘0.208333333 as shown below.

To display the decimal numbers in Time choose an output cell. After that, select the drop-down ‘Number Format’ menu in the ‘Number’ category and choose the option ‘Time’.

To set the custom time format, press the arrow button (Number Format) in the upper right corner in the number group of ribbon. Or use Ctrl+ to launch the dialog box for Format Cells.

Under the tab for Numbers click on the ‘Time tab, and select one of the formats for time and then click “OK”.

You can also click on the tab ‘Custom’ and enter one of the following codes, then click “OK” to apply the appropriate time formatting:

If the output shows ##### error this means that the output cell isn’t large enough and the output is negative. In the event that it is negative we’ll examine how to deal with the negative time in Excel in one of the sections below.

Count Hours, Minutes, or Seconds Between Two Times

Sometimes, we just need to determine the difference in time in specific units of time (hours minutes, hours, and seconds). When you subtract time, Excel will always return decimal numbers. The result is that each whole number represents a day , and decimal numbers are able to be transformed into minutes, hours, or seconds. To convert them, you can use the formulas below:

Find Hours Between Two Times

To determine the time difference between two hours in a row Use this formula:

=(End date / Start Time) * 24

Example:

=(B3-A3) * 24

The starting time is A3 while the end time is B3. It is possible to subtract B3 from A3 to determine what is the gap between these two dates, and then divide it in 24, (which is the amount of hours per day). We get 10.17 as the final result.

You can make use of the INT function determine the total number of hours:

=INT((B3-A3) * 24)

The INT function is used to round up the result, giving complete numbers.

Find Total Minutes Between Two Times

To determine the time difference between two minutes in a row Use this formula:

=(End date / Start Time) * 1440

or

=(End time Start Time ) * 24 * 60

Example:

=(B3-A3) * 1440

The starting time is A3 while the end time is B3. It is possible to use simple subtraction B3-A3 to determine what the differences are between these two dates, and then multiply by 1440, which is the amount of minutes in the day. The output is 610, which is the sum of minutes that B3 and A3 have.

Find Total Seconds Between Two Times

Utilize this formula to calculate what is the ratio between 2 seconds of time:

=(End Time / Start) * 8400

or

=(End date / Start Time) *24 * 60 * 60

Example:

=(B3-A3) * 86400

The starting time is A3 while the end time is B3. It is possible to use simple subtraction B3-A3 to determine what the differences are between these two dates, and then multiply it by 86400, which is the amount of seconds per day. The output is 36600 which is the total amount of seconds that pass between B3 and A3.

Calculate the Elapsed Time using the Start Time to Now by using the () function.

If you utilize the NOW function within Excel it will show the current date and time on your computer. If you’re looking to determine the time that has passed from the beginning time to the present, i.e., current time, you can utilize this Now() method instead of using the ending time of the formula.

Here’s a formula that you can apply to determine the time that has passed:

=Now-Start time

In the following example the beginning time is shown in column A. To calculate the time from the beginning time to the present time, you can apply the formula:

=NOW()-A1

The resultant value for time that has passed since the moment is displayed to column B. If your start times are only time units with no date dates, this formula could produce the incorrect results as illustrated below. This is because Excel will interpret the date as January 1st, 90 by default.

To correct this issue problem, you can orally add the day part to the beginning time or use the INT() function to reduce the value for the day that is returned using the NOW() method.

Then, you must enter the starting date and time in cell(s) and then use using the exact formula. Be sure to use the correct formatting for the time to the column C (result column), h:mm.

If the time elapsed is greater than 24 hours, you have to use the format of time to show the days and hours in the end. You can choose any of the formats below to display days:

In the following example in the below example, we have added the "d "day" h:mm:ss format to the C column. You can observe that the total time from the beginning time (7/29/2022) to today is 1 day, fifteen hours, 57 mins, and 29 seconds.

Calculate Time Difference Between Specified Times

You can easily determine the time difference between two times by with the TIMEVALUE() function. By using the TIMEVALUE function you do not need to input the times of start and finish in two separate cells, you can simply pass the values straight to the formula.

The Syntax:

=TIMEVALUE("Start Time") - TIMEVALUE("End Time")

For instance, to calculate the difference in time between 6:10 and 3:00, apply the formula below:

=TIMEVALUE("6:10 PM")-TIMEVALUE("3:00 PM")

Calculate the Difference in Only One Time Unit

You can utilize the HOUR() or minutes(), as well as Second() methods to determine the difference in time between two times within one unit, while leaving out other functions.

To determine the time difference in hours, ignoring seconds and minutes, apply this formula sample:

=HOUR(A2-B2)

To determine the time difference in minutes, ignoring seconds and minutes, apply this formula sample:

=MINUTE(A2-B2)

To determine the difference in time in seconds, ignoring minutes and seconds, apply this formula sample:

=SECOND(A2-B2)

If the final time is not equal to the beginning time it will result in an negative value. Therefore, you’ll get an NUM! error as the result.

Check that the output from the HOUR() function doesn’t end after 24 hours later and that the results of the MINUTE() or Second() functions won’t be exited after 60 minutes or 60 seconds, respectively.

Subtract Time to find Time Difference by using the Text Function

If you do not want to alter the formatting for the cell, you could make use of the TEXT function in order for calculating and outputting the time differences in text strings. The TEXT function is a great option because you don’t have to add formatting to the cells. You can simply specify the output format right in the formula.

The syntax used to calculate the time-difference:

=TEXT(End Time - Start Time,"format")

Time The difference in hours Between Two Times

To determine only the time that has passed between the two dates:

=TEXT(B2-A2,"hh")

In the aboveexample, the term hh is the unit for hours. It is evident this formula subtracts the times in B2 and the A2 and outputs only the hours that have passed between the beginning and ending times.

Time Different Minutes in two times

To determine only the number of minutes that have passed between the two dates, use this formula:

=TEXT(B2-A2,"[mm]")

The number [mmis the number of minutes. You can observe this formula subtracts the duration of B2 by the A2 and outputs only the amount of time that has passed between two times.

Time Different in Seconds between two times

To determine only the number of seconds that have passed between the two times, apply this formula:

=TEXT(B2-A2,"[ss]")

Where [ssis the number that is the total amount of time. This formula subtracts ending time from the beginning time and returns only an overall number of seconds.

You may have noticed that the’mm or’ss formats are enclosed within square brackets. This is because we’re trying to calculate all the seconds or minutes between the two times , and not only the remaining minutes or seconds that are not hours. If the number of hours exceeds 24 hours, then you can make use of the “[hh]” (enclosed in brackets) form within the formula.

To determine the remaining minutes between two times in addition to hours, you can use the following formula instead

=TEXT(B2-A2,"mm")

To determine the remaining seconds between two times in addition to hours, you can use the following formula instead

=TEXT(B2-A2,"ss")

To find the entire three hours, minutes and seconds in at least twice, you can use this formula:

=TEXT(B2-A2,"hh:mm:ss")

NOTE: The result returned by the TEXT function is an text. Therefore, the output can’t be used in any other calculations. If you plan to utilize the calculated time in any other calculation, you must employ one of the alternative methods to calculate your time-difference.

If the value returned by the text function is negative You will get a #VALUE! error.

Calculate Time Difference Between Two Dates

Before we get started we must be aware that Excel records Data as well as Time in serial number. When you type in the word Date within an Excel cell it assigns an identifier, and when you type in a time the system assigns the fraction of a number.

Excel uses an underlying serial number system that has dates that start from 1 January 1900 until today and beyond. That means that 1 January 1900 equals 1, 2 January 1900 equals 2, 3 January 1900 equals 3 and so on. For instance the serial number for 8/1/2022 is 44774 while the fraction, or decimal for the 6AM time zone will be 0.25 (quarter of a day).).

However, older version that came with Excel for Mac use an 1904-based date format. There is also the option to switch on this 1904-based date format. In these cases, the serial number will begin with the date 1 January 1904 (0). Then 2 Jan 1904 will be 2, 3 January 1904, etc.

Calculate Time Difference Between Two Dates in Hours

To determine the time difference in between dates or times you can apply the following formula:

=INT((B2 - A2) * 24) & " hours"

In this case, B2 is the end date, and A2 is the date of start. This Formula (B2-A2) produces an amount. It is necessary to add to the days plus the fraction to calculate the hour difference.

Let’s see how this formula functions:

You can observe, the overall difference in the dates (when you added ‘d h:mm time formatting on the cell that outputs) will be 2 days 5 60 minutes as well as 40 seconds.

You can use this customized formatting by entering ‘dh:mm:ss’ into the dialog box for Format cells.

Excel calculates the days in terms of days. It it shows that there are two days that differ in the two dates i.e the 48-hour period. We have 5 hours, 59 mins, as well as 40 seconds. We also have to incorporate this into the output.

In order to do this it is necessary to translate the five hours, 59 mins and 40 seconds in the terms of days. Since our time is measured in seconds, we’ll calculate five hours, 59 mins, as well as 40 seconds, in the form of seconds, as illustrated below:

• Five hours * 60 mins * 60 second = 18,000 secs
• 60 seconds * 59 minutes = 3,540
• 40 seconds
• Five hours and 59 mins, and 40 seconds equals 18,000 seconds plus 3,540 seconds plus 40 second = 21,580 minutes.

To determine the decimal value for each day’s duration, we have divide the above number of 21,580 seconds by 86.400 seconds (i.e. how many seconds that pass in one day).

• 21,580 / 86.400 = 0.249769 days

Therefore, the percentage we’ll get will be 0.249769 days.

The final variation between these dates will be 2 days plus 0.249769 equals 2.2497685.

Alternatively,

• For the start date time (8/1/2022 2:15:50 pm) is 44774.59433.
• For the end date of this event, the serial number (8/3/2022 8:15:30 pm) will be 44776.8441.
• End DateTime (B2) – Start DateTime (A2) = 44774.59433 – 44776.8441 = 2.2497685.

To convert the output above into hours, simply multiply the output by 24,

• 2.2497685 * 24 = 53.994444 hours.

Then, using the function INT, the decimals at the end , and the operator ‘&’ is used to add the text string (hours) to the final, giving the total hours that have passed between both days. This is 53 hours’.

Calculate Time Difference Between Two Dates in Minutes

The process of calculating the difference in time between dates using minutes is comparable to the calculation of hours. The sole difference being that you have to multiply the difference in time times 1440 (number of minutes per day) in the formula. After that, you need to add the’minutes’ text string at the end , instead of ‘hours’..

To determine the time difference of two days in terms of minutes:

=INT((B2 - A2) * 1440) &" minutes"

Where B2 is EndDate and A2 is the StartDate. In the formula above the output time of subtraction differs between dates by multiplying it by 1440 (the number of minutes that are consumed in one day). Then the string’minutes are added to the final result.

Calculate Time Difference Between Two Dates in Seconds

Calculating Seconds is exactly the same as the calculation of hours and minutes. The sole difference being that instead of calculating seconds, you will multiply the output of subtraction by the number 86400 (number of seconds per day).

To accomplish this, you’ll need to apply the following formula:

=INT((B2 - A2) * 86400) &" seconds"

This formula subtracts the datetime of B2 from A2 and multiplying the result by the number 86400 (number of seconds within a single day) since we want to see the result to be expressed in terms of seconds. This gives 194379 as the final result. Since there aren’t any decimal numbers The function INT will produce the exact result (total amount of seconds in between the two dates).

Calculate all Days, Hours, Minutes, and Seconds Between Two Dates

If you wish to determine the difference in time from two times in the form of hours, days minutes, seconds, or days it is necessary to make use of the TEXT function in conjunction using the IN function.

To determine the number of the number of days, hours, minutes and Seconds between two Dates, use the following formula:

=INT(B2-A2) and " days, "& TEXT(B2-A2,"h"" hours, ""m"" minutes as well as ""s"" minutes, and ""s" """)

The first part of the formula operates exactly the same way as earlier examples. The INT function generates an integer number by subtracting the dates given (2.2497685) which equals 2 days.

In the second part of the formula, the function TEXT subtracts both dates, and then uses custom-designed number formatting to produce the final results (5:59:40 am). It is important to note that the "h" format gives the hours, while the "m" returns the number of minutes, while "s" returns the number of seconds. The TEXT function can also add the strings inside double quotes for hours minutes, seconds, and hours following each time unit.

In the end all the INT and the TEXT function outputs are combined with the concatenation (and) operator, which gives the following result: 2 days 5 60 minutes as well as 40 second.

You may be aware, we put spaces after each word in our formula. Be sure to include those spaces to avoid results being mixed up.

Alternately, you could make use of the INT function using distinct HOUR or MINUTE SECOND functions for the identical outcomes:

=INT(B2-A2) and " days, " & HOUR(B2-A2) and " hours, " & MINUTE(B2-A2) and " minutes and " & SECOND(B2-A2) and " seconds"

Subtracting Hours, Minutes or seconds from a specific time

Sometimes, we might need to subtract specific intervals of time (Hours Minutes, Seconds, Hours) from a particular date or time. This can be accomplished using or without the TIME function within Excel. If the hours you wish to subtract are lower than or equivalent 23 hours, you may apply the formula for TIME. However, there’s a different formula that could be used to subtract any number of hours.

Subtracting any Hours from a Time with no the TIME function

For example, if you have a specific date and time and want to determine the time that occurred a certain amount of hours prior to it, you could divide the number of hours you wish subtracted by total minutes (per per day) and then add the results to the specified time.

=Given Time/DateTime (Hours that are subtracted by 24)

Example:

=A2 - (18 / 24)

Here’s how it is put into practice:

Excel determines this difference using days, therefore we must provide Excel with the amount of days (or the fraction) days we wish to subtract from the given day or date (A2).

To determine the number of days in a day to calculate days, you need to divide the number of hours you want in order to subtract the 24 (total amount of time per day) – 18/24. This gives us 0.75. If you wish subtract more hours than 24, the value prior to the decimal is days, and the number following the decimal is time.

Then Excel is subtracting 0.75 from the (44774.59433) that is the serial number for 8/1/2022 at 2:15:50 pm (A2) due to the fact that Excel is a thinker in terms of decimals and fractions.

It will return 44773.84433 that is the number of serial numbers of ‘7/31/22 8:15 pm’. Therefore, the output is 7/31/22 8:15 pm.

Example 2:

Let’s say you want to return 42 hours back in time starting on the 1st of August, 2022 to 6:06 pm. Therefore, we must provide Excel with the days we wish to subtract from the specified date.

To convert times into hours, we have to divide the 42 times 24, and then subtract that number from A2:

=A2-(42/24)

When we divide 42 by 24 we receive 1.75 days. As we have mentioned previously, the fraction before the decimal is the days (1) while the number following the decimal is the hours (0.75).

If we multiply 0.75 with 24 (0.75*24) We will receive 18 hours. Therefore, Excel will subtract 1 day and 18 hours from the A2 (August 1 2022, 6:00 pm). The output will be 7/30/22 at 8:15 pm.

The above simple math formula is able to be used to calculate any amount of time starting from the time you specify it is possible to apply the TIME formula when you want to subtract lower than or equivalent to 23.

Subtracting hours from a time by using the TIME function

The TIME function lets you transform a single value of time into individual minute, hour and second units. It is also able to combine individual units of time into a single time value. However it is important to note that the TIME function is able to handle up to 23 hours, 59 mins, as well as 59 second.

Syntax:

= Given Time/DateTime + TIME(Hours that are subtracted 0, 0)

Example:

For a trip back to 18 hours in time starting on 15 August 2022, evening (6 6) Use this TIME formula:

=A2 - TIME (18, 0, 0)

The TIME function is a three-part operation that requires three arguments which are hour, minute and second. We’re trying to subtract 18 hours, therefore one argument will be ’18’, as are the two other arguments minutes and second are defined as zero.

Typically, TIME function returns an output that ranges from zero (0:00:00 to 12:00:00 am) up to 0.99988426 (11:59:59 P.M.). In the formula above it returns 0.75 that is then subtracted by the A2.

Subtracting Minutes from the DateTime

You can apply the formulas above to subtract minutes. The sole difference being that you have divide the minutes to be subtracted by 1440.

= A2 - (4500/1440)

In the formula above the amount of time we wish to subtract (4500) is divided by the total number of minutes within one calendar day (1440) for the conversion of minutes to days. The result of this division is 3.125 days, which is removed from the A2 and we’ll get the result of 7/29/22 at 3:00 pm..

Subtracting Minutes from a specific Time by using the TIME function

It is also possible to use TIME to use the TIME function for subtracting minutes of a particular time , provided that the duration of time that is subtracted is not less than or equal to 1439 due to the TIME function’s limitations. It is able to handle 23 hours, 59 mins and 59 seconds, which amounts to 1439 minutes. If you wish remove more time than that, you have to apply the formula above.

Subtract 900 minutes of the specified time by using the function TIME:

=A2 - TIME(0,900,0)

In the formula above the number of minutes (900) we wish to subtract are specified as the 2nd argument to the function TIME. We get the August 1, 2022 date at 3:00 am.

Subtract seconds from the DateTime

It is possible to use the formulas above to subtract seconds. The sole difference in the formula is that denominator of the bracket is the number 86400.

Syntax:

= A2 (seconds to be subtracted/ 8400)

In the formula above, the number of seconds we wish to subtract (10000) is divided by the total number of seconds per one day (24 hours , 60 mins and 60 secs = 86400) to convert seconds into days. The division will result in 0.115740741 days. When subtracted by A2 we will be left with “8/1/22 3:13 PM”.

Subtracting Seconds from a specific Time by using the TIME function

Again, you can also utilize TIME to use the TIME function for subtracting seconds off a particular time, provided that the duration of time that is subtracted is not less than or equal to 86399 due to the TIME function’s limitations. It is able to handle 23 hours, 59 mins and 59 seconds, which amounts to 86399 minutes. If you wish remove more than 86399 seconds you have to apply the formula above.

Subtract 9000 second from specified time by using the function TIME:

=A2 - TIME(0,9000,0)

In the formula above the second (9000) we wish to subtract are specified as the 2nd argument to the function TIME. We get the date of August 1, 2022 at 3:00 pm.

Subtracting hours, minutes, and seconds from a specific time using the TIME function

If you’re looking remove hours, minutes, or seconds off a specific date using the TIME function is a great tool.

For instance, to subtract 2 hours 15, 15 minutes as well as 30 seconds use this formula:

=A2 - TIME(2,15,30)

In the formula above we have specified all three time units, namely minutes, hours, as well as seconds in TIME functions arguments. Thus, the formula subtracts 2 hours 15 minutes, as well as 30 second from the A2 (8/1/22 6:00 pm). We will get 8/1/22 at 3:44 pm.

Calculating Negative Times in Excel

If the end Time is less that the start Time and you are able to receive a negative value in your time differential. The formula will produce negative numbers when the time difference is not greater than. This is usually the case when you enter only dates and times into spreadsheets without dates.

If you typically work during the night, your date and time of your end will always be later than the date of your start and the time you work. For instance, if you begin your work at 6 pm and finish at 6 PM and leave the next morning at 6 am The normal subtraction method will not work.

As you will see below it’s not just the formula that will not work, but Excel will give you an ###### error if you are running a negative time.

If you’d like to show the positive in any way it is possible to change into the date format 1904. The most modern Excel versions default on the date format 1900,, however when you change your date format to 1900 you will be able to show the negative values of time.

Here’s how to switch to the 1904 system of date within MS Excel:

To do this, click the File menu in the Excel window, and then click Options from the view of backstage.

Within in the Excel Options window, go to the tab ‘Advanced’ to the left. then scroll to the ‘When you calculate this section of the workbook’. After that, select the checkbox for ‘Use the 1904 date system and click OK.

Then, Excel will display the negative values for the date and time as follows:

If you wish to show time as negative numbers You can make use of the IF function to make negative times appear correctly.

You can apply one of the formulas below to show negative times correctly at midnight when the times are overlapping:

=IF((B3-A3)<0,1-(A3-B3),(B3-A3))

or

=IF(B3>A3,B3-A3,1-A3+B3)

The formula above will calculate that negative difference in time, and show it in the proper way as illustrated below.

However, the formula above is generally applicable. If the difference in time exceeds 24 hours, then the above formulas will not work. In these cases, be sure that you include dates in the time.

Subtracting the Dates to Count Days in Between

Like time, you can subtract two cells containing dates to calculate the days the interval between dates.

For instance Cell A3 and B3 contain two dates. To determine the differences in days, apply the formula below:

=B3-A3

The formula calculates the difference between two dates, A3 and B3 as 132 calendar days.

You can count days between two Dates by using the DAYS function

In addition to the subtracting of cell numbers, you could use the function DAYS to determine how much difference there is between dates.

Its syntax

=DAYS(End date and Beginning Date)

Example:

=DAYS(B3,A3)

Where B3 is the End Date and A3 is the Start Date.

This is the end. You now know all you must know about subtracting time using Excel and more. It is hoped that knowing these functions will boost the efficiency of Excel.