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 a 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 it to numbers in Excel.

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

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

Suppose we have a dataset of some Employee Names, Entry Time, and Exit Time. Now we will subtract time and convert it to a 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-

• 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.

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

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

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

• 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.

Read More: How to Subtract 30 Minutes from a Time in Excel

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-

• 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.

• In a similar way, you can subtract minutes too. Just choose a cell (F5) and apply the formula below-

• 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-

• 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 the blink of an eye. Simple isn’t it?

Read More: How to Subtract Hours from Time in Excel

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-

Where,

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

• 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.

• 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-

• 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.

Read More: How to Calculate Time Difference in Numbers

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-

Where,

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

• 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-

Where,

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

• Thus the final output in minutes will be ready.

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

Where,

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

• 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.

Read More: How to Calculate Difference Between Two Times in Excel

5. Use the 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-

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

• 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 Subtract Minutes from Time in Excel

6. Apply the NOW Function to Subtract Time and Convert it to Numbers

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-

• 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.

Read More: How to Subtract Military Time 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-

Where,

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

• 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 Subtract Date and Time 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.

Download Practice Workbook

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

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 are always responsive to your queries. Stay tuned and keep learning.

Related Articles

• How to Calculate Time Difference in Minutes in Excel
• [Fixed!] VALUE Error (#VALUE!) When Subtracting Time in Excel
• How to Subtract and Display Negative Time in Excel

<< Go Back to Subtract Time | Calculate Time | Date-Time in Excel | Learn Excel