Microsoft Excel has a wide range of options to subtract minutes from time using different formulas. We will discuss those methods of subtraction in this tutorial.
How to Get Current Time in the Desired Format in Excel
We can subtract hours, minutes, or seconds from any time in Excel. First, we input the current time using the NOW function. Here, we will show the subtraction of minutes from time only.
Step 1:
- Go to Cell B5 and enter the NOWÂ function.
=NOW()
Step 2:
- Press the Enter button.
Both time and date values are shown here. We want time values only. Let’s change the format of the cell.
Step 3:
- Press Ctrl+1 now.
- Set h:mm:ss AM/PM format from the Custom section of the Number tab.
Step 4:
- Now, press OK.
Step 5:
- We will subtract 30 minutes from the time value. Enter 30 in Cell C5.
So, our dataset is ready. Now, we will subtract time from this value in the following methods.
Note: As we have used the NOW function to get the current time, the Input time changes continuously.
1. Subtract a Fraction of Minutes from Time in Excel
In this method, we will subtract a fraction of minutes from time. First, we need to relate the minutes and the day.
We all know that
1 day = 24 hours
1 hour = 60 minutes
So, 1 minute=1/(24*60) day
=1/1440 day
So, when we subtract minutes from a time in the day unit, we will multiply the minutes by 1/1440.
Step 1:
- Move to Cell D5.
- Write the formula below.
=B5-C5/1440
Step 2:
- Now, press the Enter button.
Subtraction is performed successfully.
Read More: How to Subtract 30 Minutes from a Time in Excel
2. Using the TIME Function to Subtract Minutes from Time
The TIME function modifies any number put in the formula to a time value.
Now, execute the following steps.
Step 1:
- Go to Cell D5.
- Enter the following formula in the cell.
=B5-TIME(0,C5,0)
Step 2:
- Now, press Enter.
Read More: How to Subtract Hours from Time in Excel
3. Combining the TIME, HOUR, MINUTE, and SECOND Functions to Subtract Minutes from Time
The HOUR function returns a range of numbers 0-23 in hour format.
The MINUTE function converts values 0 to 59 in minute format.
The SECOND function works like the MINUTE function, i.e. it converts values 0 to 59 in the second format.
Now, follow the steps below.
Step 1:
- Write the following formula in Cell D5.
=TIME(HOUR(B5),MINUTE(B5)-C5,SECOND(B5))
Step 2:
- Then click the Enter button.
Read More: How to Subtract Military Time in Excel
4. Use of the MOD function to Subtract Minutes
The MOD function provides remainders after division.
We will apply this MOD function in this method. As the minute input is in the General format, we need to use the fraction formula. But when the minute input is in a proper format, i.e. the minute format, the fraction formula is not needed.
Step 1:
- Go to Cell D5.
- Write the following formula in the cell.
=MOD(B5-C5/1440,1)
Step 2:
- Now, press the Enter button.
We get the result by inserting the fraction formula with the MOD function.
Alternative Formula to Subtract Minutes Using the MOD Function:
We can avoid the fractionation in the formula if we follow the procedure below.
Step 1:
- Copy Row 5 and paste it into Row 6.
Step 2:
- Click on Cell C6 and press Ctrl+1 to change the format.
- Choose h:mm format and then press OK.
Look at Cell C6.
Step 3:
- Enter the following formula on Cell D6.
=MOD(B6-C6,1)
Step 4:
- Finally, press the Enter button.
Both results are the same.
Read More: How to Subtract Time and Convert to Number in Excel
5. Subtracting Minute from Time with a Simple Formula
We will perform subtraction using a simple formula. We will first change the format of the time value into h:mm.
Step 1:
- Click on Cell C5.
- Press the right button of the mouse.
- Choose Format Cells from the list.
We can also do this by pressing Ctrl+1.
Step 2:
- Choose h:mm format from the Custom section.
- Then, press OK.
This is what will look like.
Step 3:
- Write the following formula in Cell D5.
=B5-C5
Step 4:
- Press the Enter button.
Read More: How to Subtract Date and Time in Excel
6. Inserting Excel TEXT Function
The TEXT function transforms any number into text in a given format
Now carefully apply the following steps.
Step 1:
- Move to Cell D5.
- Copy and Paste the formula below.
=TEXT(B5-C5,"h:mm:ss")
Step 2:
- Press the Enter button.
Here, we select the format as h:mm:ss. But we could use any format as per our necessity.
7. Formula to Directly Subtract Minutes from Time
We can directly subtract minutes from the time by entering the subtrahend in a suitable formula.
Step 1:
- Go to Cell D5.
- Enter the formula below.
Here, we did not use any cell reference for the subtrahend.
Step 2:
- Now, press the Enter button.
We get the desired result.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
In this article, we described some easy methods on how to subtract minutes from time in Excel. I hope this will satisfy your needs. Please give your valuable suggestions in the comment box.
