Table of Contents
To subtract 30 minutes from a given time in Excel, you can use the MINUTE function to extract the minutes from the given time, subtract 30 from it, and then use the TIME function to combine the remaining minutes with the hour and seconds from the original time. This will give you the updated time with 30 minutes subtracted. Alternatively, you can use the TIME function to directly subtract 30 minutes from the given time. Both methods will result in the same outcome.
Excel: Subtract 30 Minutes from Time
You can use either of the following formulas to subtract 30 minutes from a time in Excel:
Formula 1: Use Mathematical Calculation
=B2-(30/1440)
This particular formula subtracts 30 minutes from the time in cell B2.
Note that this formula divides 30 by the number of minutes in a day (1,440) because otherwise Excel will attempt to subtract 30 days instead of minutes.
Formula 2: Use TIME Function
=B2-TIME(0, 30, 0)
This particular formula also subtracts 30 minutes from the time in cell B2.
Both formulas produce the same results and both formulas work with times and datetimes in Excel.
The following examples show how to use each formula in practice with the following dataset that contains information about the time when various projects are due:
Example 1: Subtract 30 Minutes from Time Using Mathematical Calculation
We can type the following formula into cell C2 to subtract 30 minutes from the time in cell B2:
=B2-(30/1440)
We can then click and drag this formula down to each remaining cell in column B:
The times in column C represent the times in column B with 30 minutes subtracted from them.
Example 2: Subtract 30 Minutes from Time Using TIME Function
We can type the following formula into cell C2 to subtract 30 minutes from the time in cell B2:
=B2-TIME(0, 30, 0)
We can then click and drag this formula down to each remaining cell in column B:
The times in column C represent the times in column B with 30 minutes subtracted from them.
Note that the TIME(Hour, Minute, Second) function can be used to add or subtract a specific number of hours, minutes and seconds from a time.
By using TIME(0, 30, 0) we are able to subtract exactly 30 minutes from each time.
Note: You can find the complete documentation for the TIME function in Excel .
Additional Resources
The following tutorials explain how to perform other common operations in Excel: