Excel Subtracting Minutes from Time: How-To
The calculator for this page is switched off while it is checked. The formula is below, and the worked example in the article shows how to use it by hand.
To subtract minutes from a time in Excel, use the formula =A1 - TIME(0, minutes, 0), where A1 contains the time and minutes is the number of minutes you want to subtract. For example, =A1 - TIME(0, 30, 0) subtracts 30 minutes. This article explains the formula, how to handle times that cross midnight, and common pitfalls.
Formula
The core formula is:
New time = Start time - TIME(0, minutes, 0)
In Excel, times are stored as fractions of a day (0 to 1). The TIME function returns a decimal representing a given hour, minute, and second. TIME(0, minutes, 0) converts the number of minutes into that fraction. Subtracting this from the start time gives the new time.
- Start time – a cell containing a time value (e.g., 9:00 AM).
- minutes – the number of minutes to subtract (can be a cell reference or a number).
- Result – a time value; format the cell as a time to display correctly.
Worked Example
Using the default values from the calculator above: start time = 9:00 (9:00 AM), subtract 45 minutes.
- Convert 45 minutes to a fraction of a day: 45 / 1440 = 0.03125 (since 24*60 = 1440 minutes per day).
- Start time as a fraction: 9:00 AM = 9/24 = 0.375.
- Subtract: 0.375 – 0.03125 = 0.34375.
- Convert back to time: 0.34375 * 24 = 8.25 hours, which is 8:15 AM.
So the result is 8:15 AM. The calculator above gives the same answer.
How to Subtract Minutes in Excel (Step-by-Step)
There are several ways to subtract minutes, but the TIME function is the most straightforward.
- Using TIME function: In a cell, type
=A1 - TIME(0, 30, 0)to subtract 30 minutes from the time in A1. - Using direct division: Since 1 minute = 1/1440 of a day, you can use
=A1 - 30/1440. - Using cell references: Put the number of minutes in a cell (e.g., B1) and use
=A1 - TIME(0, B1, 0).
After entering the formula, format the result cell as a time (e.g., h:mm AM/PM) to display it correctly.
Handling Times That Cross Midnight
If you subtract minutes from a time early in the day, the result may be negative (e.g., 2:00 AM minus 30 minutes). Excel will show a series of # symbols or a negative time, depending on your settings.
To fix this, use the MOD function to wrap around midnight:
=MOD(A1 - TIME(0, minutes, 0), 1)
This returns a valid time between 0 and 1, so 2:00 AM minus 30 minutes becomes 1:30 AM (not -0.03125).
Common Mistakes and Pitfalls
- Not formatting the result as a time: If the cell is in General format, you’ll see a decimal like 0.34375 instead of 8:15 AM.
- Using minutes as hours:
TIME(30, 0, 0)subtracts 30 hours, not 30 minutes. Always put minutes in the second argument. - Ignoring midnight crossing: Without
MOD, subtracting across midnight gives an error or negative time. - Mixing time and text: If your “time” is text (e.g., “9:00”), the formula won’t work. Use
TIMEVALUEto convert text to a time.
Alternatives: Using MINUTE and SECOND Functions
You can also subtract minutes using the TIME function with hour and second arguments set to 0, as shown. Another approach is to use TIMEVALUE for text times:
=TIMEVALUE("9:00 AM") - TIME(0, 45, 0)
For subtracting a time duration (e.g., 1:30) from a time, use =A1 - B1 where B1 contains the duration formatted as [h]:mm.
Frequently Asked Questions
How do I subtract 30 minutes from a time in Excel?
Use =A1 - TIME(0, 30, 0) where A1 is the time cell. This subtracts 30 minutes. If the result is negative, wrap with MOD: =MOD(A1 - TIME(0, 30, 0), 1).
Why does my result show as a decimal number?
Excel stores times as fractions of a day. If the cell is not formatted as a time, you’ll see a decimal. Right-click the cell, choose Format Cells, select Time, and pick a format like 1:30 PM.
How do I subtract minutes across midnight?
Use the MOD function: =MOD(A1 - TIME(0, minutes, 0), 1). This ensures the result stays within a 24-hour cycle. For example, 1:00 AM minus 30 minutes gives 12:30 AM.
Can I subtract minutes from a time that includes seconds?
Yes. The TIME function works with seconds too: =A1 - TIME(0, 0, 30) subtracts 30 seconds. For minutes, just use the minutes argument.
What if I need to subtract a duration like 1 hour 30 minutes?
Use =A1 - TIME(1, 30, 0) or reference a cell containing the duration. For durations over 24 hours, format the result as [h]:mm to show total hours.