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

Get FREE Advanced Excel Exercises with Solutions!

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.

how to subtract 30 minutes from a time in excel

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.

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

  • Now, select cell E5.
  • Then, write down the following formula.
=D5-C5

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.

Using Format Cells Option

  • 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.
=E5-F5

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

  • Correspondingly, press ENTER.

Using Arithmetic Operator

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

1 day = 24 hours
1 hour = 60 minutes
So, 1 day = (24 60) = 1440 minutes
Therefore, 1 minute = 1 / 1440 day

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.
=E5-F5/1440
  • As always, hit the ENTER key.

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

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.
=E5-"00:30"
  • Consequently, press ENTER.

Directly Subtract 30 Minutes from a Time in Excel

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.
=E5-TIME(0,F5,0)
  • Hence, tap ENTER.

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


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.
=TIME(HOUR(E5),MINUTE(E5)-F5,SECOND(E5))
  • Lastly, press ENTER.

Combining TIME, HOUR, and MINUTE Functions

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.
=TEXT(E5-F5,"h:mm")
  • Later, tap ENTER.

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

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.
=MOD(E5-F5/1440,1)

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

  • Lastly, hit the ENTER key.

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


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.

Applying VBA Code

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

Inserting Code Module

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

Applying VBA Code to Subtract 30 Minutes from a Time in Excel


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

Shahriar Abrar Rafid

Shahriar Abrar Rafid

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc in Engineering from the Bangladesh University of Engineering & Technology. I am a Naval Architecture and Marine Engineering graduate with a great interest in research and development. I love reading books & traveling. Always try to gather knowledge from various sources and implement them effectively in my work.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo