Table of Contents
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 .