How to Subtract Time and Convert to Number in Excel

While working in Microsoft Excel sometimes we need to subtract time and convert it to number so that we can calculate the time difference. But it might become difficult for you while converting to numbers. Today, in this article, I am sharing with you how to subtract time and convert to number in Excel.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


7 Easy Methods to Subtract Time and Convert to Number in Excel

In the following, I have shared 7 easy methods to subtract time and convert to number in Excel.

Suppose we have a dataset of some Employee Names, Entry Time, and Exit Time. Now we will subtract time and convert to number to determine the Total Working Time using some simple tricks. Stay tuned!


1. Utilize Subtraction Formula to Subtract Time and Convert to Number

The simplest way to subtract time is by using a simple subtraction formula. After subtracting the time difference we can multiply them with numeric values to convert them to hours and minutes. Follow the instructions below-

Steps:

  • First, choose a cell (E5) and write the following formula down-
=(D5-C5)*24

Utilize Subtraction Formula to Subtract Time and Convert to Number

  • Second, press ENTER and drag the “Fill Handle” down to fill all the cells.
  • Within a moment, we successfully subtracted time and extracted the total hour from the time difference.

Utilize Subtraction Formula to Subtract Time and Convert to Number

  • Next, we will determine the total minutes from the given time. For that,
  • Simply, select a cell (F5) and apply the formula below-
=(D5-C5)*24*60

  • Similarly, press ENTER and pull the “Fill Handle” down.
  • Here we have the total minutes in our hands.

Utilize Subtraction Formula to Subtract Time and Convert to Number

  • Now, let’s calculate the total minutes with this simple formula.
  • In the same fashion, choose a cell (G5) and apply the formula down-
=(D5-C5)*24*60*60

  • Similarly, hit ENTER and drag the “Fill Handle” down to fill the cells.
  • Finally, you will get the total working minutes for all the employees in a new column.

Utilize Subtraction Formula to Subtract Time and Convert to Number

Read More: How to Convert Time to Number in Excel (5 Easy Methods)


2. Using TEXT Function

If you want you can apply the TEXT function to subtract time and convert to numbers at the same time. Here the TEXT function will convert the subtracted time from the string to a specific format. Just go through the steps below-

Steps:

  • Just like the previous method, choose a cell (E5) and write the formula down-
=TEXT(D5-C5,”hh”)

Using TEXT Function to Subtract Time and Convert to Number in Excel

  • Hit ENTER and then pull the “Fill Handle” down to fill the whole column.
  • As a result, the total working hours will be determined from the list.

Using TEXT Function to Subtract Time and Convert to Number in Excel

  • In a similar way, you can subtract minutes too. Just choose a cell (F5) and apply the formula below-
=TEXT(D5-C5,"[mm]")

Using TEXT Function to Subtract Time and Convert to Number in Excel

  • Then, click ENTER and drag the “Fill Handle”.
  • Thus the time difference from the columns will be converted to minutes.

  • Sometimes you might want to get the subtracted hour and minute in the same column. Well, using the TEXT function you can achieve that too.
  • Just select a cell (G5) and put the below formula down-
=TEXT(D5-C5,"h:mm")

  • Hence, press ENTER and drag the “Fill Handle” down to fill the column.
  • In summary, you will get the subtracted hour and minute in the same column within a blink of an eye. Simple isn’t it?

Using TEXT Function to Subtract Time and Convert to Number in Excel

Read More: How to Convert String to Number in Excel VBA (3 Methods)


Similar Readings


3. MOD Function to Subtract Time and Convert to Number

With the help of the MOD function you can subtract time and convert directly to numbers within seconds. Follow the steps below-

Steps:

  • Presently, choose a cell (E5) and apply the formula-
=MOD(D5-C5,1)*24

Where,

  • The MOD function will find the divisor between the times and multiply with the numeric value (24) to convert to numbers.

Apply MOD Function to Subtract Time and Convert to Number in Excel

  • Hit ENTER and pull the “Fill Handle” down.
  • Finally, the total working time will be in our hands. Here, in cell (E5) the output is 50 which is displaying the total subtracted time as 9 hours and 50 minutes.

Apply MOD Function to Subtract Time and Convert to Number in Excel

  • You might be wondering about how to extract only hours from the given time in the worksheet. Well, I have a simple solution for this. Using the INT function you can round the output to the nearest integer.
  • Just select a cell (F5) and write the following formula down-
=INT((D5-C5)*24)

  • In the same fashion, click ENTER and pull the “Fill Handle” down.
  • Here, the nearest integer shows only the total hours between the given times.

Apply MOD Function to Subtract Time and Convert to Number in Excel

Read More: Excel Convert to Number Entire Column (9 Simple Methods)


4. HOUR, MINUTE, and SECOND Functions to Subtract and Convert to Number

In some situations, you can utilize the HOUR, MINUTE, and SECOND functions to subtract and convert to numbers the time difference.

Steps:

  • Simply, choose a cell (E5) and write the below formula down-
=HOUR(D5-C5)

Where,

  • The HOUR function will extract only hours from the given time difference.

Use HOUR, MINUTE, and SECOND Functions to Subtract Time and Convert to Number in Excel

  • Next, hit ENTER and pull the “Fill Handle” down.
  • Within a moment, the subtracted time with conversion to hours will be ready.

  • Similarly, in a new column let’s determine the total minute. For this, select a cell (F5) and put the below formula inside the cell-
=MINUTE(D5-C5)

Where,

  • The MINUTE function will convert the time fraction to minutes.

  • Thus the final output in minutes will be ready.

Use HOUR, MINUTE, and SECOND Functions to Subtract Time and Convert to Number in Excel

  • This time we will calculate the total seconds for the subtracted time. Thereafter, choose a cell (G5) and put the below formula-
=SECOND(D5-C5)

Where,

  • The SECOND function will convert a given time to seconds.

Use HOUR, MINUTE, and SECOND Functions to Subtract Time and Convert to Number in Excel

  • Hence, press ENTER from the keyboard and pull the “Fill Handle” down.
  • Finally, we have got both our results the subtracted time and conversion to seconds.

Use HOUR, MINUTE, and SECOND Functions to Subtract Time and Convert to Number in Excel

Read More: How to Fix Convert to Number Error in Excel (6 Methods)


Similar Readings


5. Use IF Function to Subtract and Convert to Number

By proper utilization of the IF function you can get the difference between a starting and end time. Go through the instructions below-

Steps:

  • First, select a cell (E5) and write the formula down-
=IF(D5>=C5, D5-C5, D5+1-C5)*24

Where,

  • The IF function will go through the condition which is (D5>=C5)and give an output of (D5-C5). If the condition is not met it will display an output calculation of (D5+1-C5).

Use IF Function

  • Similarly, hit ENTER and pull the “Fill Handle” down.
  • In conclusion, the final output will be converted to numbers within a blink of an eye.

Read More: How to Convert Hours and Minutes to Decimal in Excel (2 Cases)


6. Apply NOW Function to Subtract Time and Convert to Number

While subtracting time you might also need to determine time from the present time. Thus you need to apply the NOW function. The NOW function will return the current time in a given string.

Steps:

  • Choose a cell (D5) and write the below formula-
=NOW()-C5

Apply NOW Function to Subtract Time and Convert to Number

  • In the same fashion, click ENTER and drag the “Fill Handle” down.
  • Within a moment you will get the subtracted time in a new column.

Apply NOW Function to Subtract Time and Convert to Number

Read More: How to Convert Degrees Minutes Seconds to Decimal Degrees in Excel


7. Use TIME Function

If you want to subtract a fixed amount of time from a given time you can use the TIME function in Excel. The TIME Function helps to create a fixed amount of time consisting of hours, minutes, and seconds.

Steps:

  • Presently, select a cell (D5) and put the following formula down-
=C5-TIME(1,30,0)

Where,

  • The time function will subtract 1 hour 30 min from the given time in cell (C5).

Use TIME Function

  • Just hit ENTER and drag down the “Fill Handle”.
  • In conclusion, we have successfully subtracted time and converted to numbers within a short time. Simple isn’t it?

Read More: How to Convert Degrees Decimal Minutes to Decimal Degrees in Excel


Things to Remember

  • Don’t forget to change the cell format from the “Format Cells” feature to get the exact format you are looking for.

Practice Section

For practice, you can check the worksheet where we have added a sheet named “Practice”. Try by yourself.


Conclusion

In this article, I have tried to cover all the methods to subtract time and convert to numbers in Excel. Take a tour of the practice workbook and download the file to practice by yourself. I hope you find it helpful. Please inform us in the comment section about your experience. We, the Exceldemy team, are always responsive to your queries. Stay tuned and keep learning.


Related Articles

Wasim Akram

Wasim Akram

Hi! my name is Wasim Akram. I am a graduate in mechanical engineering from Ahsanullah University of Science & Technology. I am passionate about learning new things and writing articles. I write article about Microsoft Excel and it's my favourite thing to do.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo