How to Subtract 30 Minutes from a Time in Excel (8 Easy Ways)

This article provides the answers to your questions on how to subtract 30 minutes from a time in Excel. There are many ways to take 30 minutes out of time in Microsoft Excel. We can use various formulas to do the task. Here, we’ll discuss 8 quick & proven methods of how to subtract 30 minutes from a time in Excel.

Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.

8 Ways to Subtract 30 Minutes from a Time in Excel

For better understanding, we’ll use an Employee Time Tracker of a certain organization. Here, this dataset contains the Name of the employees along with their In Time, Out Time, and a fixed Lunch Time (in minutes) in columns B, C, D, and E respectively.

Now, we’ll calculate the Total Working Time of each employee using various approaches by subtracting the 30 minutes of Lunch Time from their residing time. So, let’s explore them one by one.
Here, we have used Microsoft Excel 365 version, you may use any other version according to your convenience.

1. Using Arithmetic Operator to Subtract 30 Minutes from a Time in Excel

In our very first method, we will perform subtraction using a simple formula. It’s simple and easy, just follow along.

📌 Steps:

• At the very beginning, create a new column Total Time between columns Out Time and Lunch Time. Basically, it sits on Column E.

• Now, select cell E5.
• Then, write down the following formula.

Here, C5 and D5 represent the In Time and Out Time of Robin consecutively.

• After that, press the ENTER key.

• Now, bring the cursor to the bottom right corner of cell E5. Thus, it’ll look like a plus (+) sign. It’s the Fill Handle tool.
• Then, double-click on it.

• Automatically, it copies the formula to the lower cells. Hence, we also get the results in the remaining cells also.

• At this moment, we’ll change the format of Lunch Time into h:mm.
• For this, select cells in the F5:F14 range.
• Later, press CTRL + 1 on the keyboard.

• Suddenly, the Format Cells dialog box opens.
• Here, go to the Number tab.
• Then, select Custom from Category.
• After that, choose h:mm from the Type list.
• Next, click OK.

• Eventually, this is what it will look like.

• Then, construct a new column Total Work Time under Column G.
• Later, select cell G5.
• After that, paste the following formula into the Formula Bar.

Here, E5 and F5 serve as the Total Time and Lunch Time of Robin.

• Correspondingly, press ENTER.

These are the Total Work Times of the employees. Simply, we subtracted the Lunch Time from their Total Time.

Read More: How to Subtract Minutes from Time in Excel (7 Methods)

2. Utilizing Fraction of Day to Subtract 30 Minutes from Time in Excel

In this method, we will subtract minutes as a fraction of day from time. For this, we need to understand the relation between minute and day.
Generally, we know that

So, when we’ll subtract 30 minutes from a time in the day unit, we will multiply the minutes by 1/1440. Therefore, let us go through the steps.

📌 Steps:

• At first, select cell G5.
• Secondly, put down the following formula into the cell.

• As always, hit the ENTER key.

Read More: How to Subtract Date and Time in Excel (6 Easy Ways)

3. Directly Subtract 30 Minutes from a Time in Excel

We can directly subtract 30 minutes from the Total Time by entering the subtrahend in a suitable formula. So, let’s have a look at the procedure’s steps below.

📌 Steps:

• Firstly, tap on cell G5.
• Secondly, put the following formula down into the cell.

• Consequently, press ENTER.

Read More: How to Subtract Time in Excel (7 Quick Methods)

4. Inserting TIME Function to Subtract 30 Minutes from a Time in Excel

In our fourth method, we’ll use the TIME function of Excel to do the task. Allow me to demonstrate the process step-by-step.

📌 Steps:

• Initially, pick cell G5.
• Secondarily, paste the following formula.

• Hence, tap ENTER.

5. Combining TIME, HOUR, and MINUTE Functions

As with Excel, there are multiple ways of doing the same task. Now, we’ll solve the problem using a combination of TIME, HOUR, and MINUTE functions. Let’s see the process in detail.

📌 Steps:

• Primarily, select cell G5.
• Secondarily, paste the following formula.

• Lastly, press ENTER.

Read More: How to Subtract Hours from Time in Excel (2 Easy Ways)

6. Employing TEXT Function to Subtract 30 Minutes from a Time in Excel

If you are curious to learn how to use the TEXT function, this section may come in handy. So, let’s see the process in detail.

📌 Steps:

• First of all, convert the format of Lunch Time into h:mm like Method 1.
• Then, select cell G5.
• After that, put the following formula into the cell.

• Later, tap ENTER.

Now, all the results in Column G are in Text format.

7. Implementing MOD Function to Subtract 30 Minutes from a Time in Excel

This approach will use the MOD function. We must apply the fraction formula like Method 2 because the minute input is in the General format. However, the fraction formula is not required when the minute input is in the right format like Method 1, which is the minute format. So, without further delay, let’s dive in!

📌 Steps:

• In the first place, select cell G5.
• Then, place the following formula into the cell.

It returns the remainder after a number is divided by a divisor.

• Lastly, hit the ENTER key.

8. Applying VBA Code

Another alternative is to employ the VBA code. Although using formulas is a quick way of editing data, it can be difficult to interpret. Furthermore, if you often need to do the task in a unique way, then you may consider the VBA code below. So, let’s begin.

📌 Steps:

• Firstly, make a new column Total Time under Column F.
• Secondarily, go to the Developer tab.
• Then, select Visual Basic on the Code group.
• Alternatively, press ALT + F11 to do the same task.

• Instantly, the Microsoft Visual Basic for Applications window opens.
• Afterward, move to the Insert tab.
• Later, select Module from the options.

• Immediately, it opens the Code Module.
• Then, write down the following code in the Module.

Sub subtract_30()
Dim in_time As Date, out_time As Date
Dim total_time As Date
For x = 5 To 14
in_time = Cells(x, 3).Value
out_time = Cells(x, 4).Value
total_time = out_time - in_time - Cells(x, 5).Value
Cells(x, 6).NumberFormat = "hh:mm"
Cells(x, 6).Value = total_time
Next x
End Sub

• Lastly, select the Run icon or press F5 on the keyboard.

• Now, return to the VBA worksheet.
• Thus, the Total Time column gets filled automatically with the correct result.

Practice Section

For doing practice by yourself we have provided a Practice section like the one below in each sheet on the right side. Please do it by yourself.

Conclusion

This article provides easy and brief solutions to subtract minutes from time in Excel. Don’t forget to download the Practice file. Thank you for reading this article, we hope this was helpful. Please let us know in the comment section if you have any queries or suggestions. Please visit our website Exceldemy to explore more.

Related Articles

• [Fixed!] VALUE Error (#VALUE!) When Subtracting Time in Excel
• Subtract and Display Negative Time in Excel (3 Methods)
• How to Subtract Military Time in Excel (3 Methods)