How to Subtract Time and Convert to Number in Excel?

We have a dataset of some Employee Names, Entry Time, and Exit Time. We will subtract time and convert it to a number to determine the Total Working Time.

Method 1 – Utilize a Subtraction Formula to Subtract Time and Convert to Number

Steps:

• Choose a cell (E5) and insert the following formula:

• Press Enter and drag the Fill Handle down to fill all the cells.

We will determine the total minutes from the given time.

• Select a cell (F5) and apply the formula below:

• Press Enter and pull the Fill Handle down.

Let’s calculate the total seconds with this simple formula.

• Choose a cell (G5) and apply the formula:

• Hit Enter and drag the Fill Handle down to fill the cells.

Method 2 – Using the TEXT Function

Steps:

• Choose a cell (E5) and insert the following:

• Hit Enter and then pull the Fill Handle down to fill the whole column.

• You can subtract into minutes with the following:

• Hit Enter and drag the Fill Handle.

We’ll get the value in hours and minutes

• Select a cell (G5) and insert:

• Hit ENTER and drag the Fill Handle down to fill the column.

Read More: How to Subtract Hours from Time in Excel

Method 3 – MOD Function to Subtract Time and Convert to Number

Steps:

• Choose a cell (E5) and apply the formula:

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.
• In cell (E5), the output is 9.50 which is displaying the total subtracted time as 9 hours and 30 minutes.

Using the INT function you can round the output to the nearest integer.

• Select a cell (F5) and insert the following formula:

• Click Enter and pull the Fill Handle down.

Read More: How to Calculate Time Difference in Numbers

Method 4 – HOUR, MINUTE, and SECOND Functions to Subtract and Convert to Number

Steps:

• Choose a cell (E5) and insert the following:

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

• Hit Enter and pull the Fill Handle down.

Let’s determine the total minutes.

• Select a cell (F5) and put the following formula inside the cell:

The MINUTE function will convert the time fraction to minutes.

• Hit Enter and pull the Fill Handle down.

We will calculate the total seconds for the subtracted time.

• Choose a cell (G5) and put the below formula:

The SECOND function will convert a given time to seconds.

• Hit Enter and pull the Fill Handle down.

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

Method 5 – Use the IF Function to Subtract and Convert to Number

Steps:

• Select a cell (E5) and insert the following:

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

• Hit Enter and pull the Fill Handle down.

Read More: How to Subtract Minutes from Time in Excel

Method 6 – Apply the NOW Function to Subtract Time and Convert It to Numbers

You might also need to determine time from the present time.

Steps:

• Choose a cell (D5) and insert the below formula-

• Hit Enter and pull the Fill Handle down.

Read More: How to Subtract Military Time in Excel

Method 7 – Use the TIME Function

We want to subtract a fixed amount of time from a given time.

Steps:

• Select a cell (D5) and insert the following formula:

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

• Hit Enter and pull the Fill Handle down.

Read More: How to Subtract Date and Time in Excel

Things to Remember

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

Download the Practice Workbook

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