Excel Subtract 5 Minutes from Time: Quick Formula
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 5 minutes from a time in Excel, use the formula =A1 - TIME(0,5,0), where A1 contains the original time. This returns the time exactly 5 minutes earlier, formatted as a time value. For example, if A1 is 3:45 PM, the formula returns 3:40 PM.
Formula
The core formula is =A1 - TIME(0,5,0). In Excel, times are stored as fractions of a day, so 1 hour = 1/24, 1 minute = 1/1440, and 1 second = 1/86400. The TIME function creates a time value from hours, minutes, and seconds. Thus TIME(0,5,0) equals 5/1440, or 0.00347222 in decimal. Subtracting this from the original time shifts it back by 5 minutes.
If you prefer a purely arithmetic approach, use =A1 - 5/1440. Both formulas produce identical results. The TIME version is more readable and less error-prone when you need to adjust multiple components (e.g., subtract 1 hour and 5 minutes with TIME(1,5,0)).
Worked Example
Assume cell A1 contains 9:00 (formatted as hh:mm). To subtract 5 minutes:
- Enter
=A1 - TIME(0,5,0)in cell B1. - Press Enter. B1 shows
8:55if formatted as time.
Let’s verify the decimal math: 9:00 AM is 9/24 = 0.375. Five minutes is 5/1440 ≈ 0.00347222. Subtract: 0.375 – 0.00347222 = 0.37152778. Multiply by 24 to get hours: 0.37152778 × 24 = 8.9166667 hours, which is 8 hours and 55 minutes (0.9166667 × 60 = 55). The result is 8:55 AM, exactly as expected.
Why Times Are Stored as Numbers
Excel treats dates and times as serial numbers. The integer part represents days since January 1, 1900, and the fractional part represents the time of day. For example, 0.5 is 12:00 PM, 0.25 is 6:00 AM, and 0.75 is 6:00 PM. This design allows arithmetic like subtraction to work naturally. When you subtract 5 minutes, you are subtracting 5/1440 of a day. If the result is negative (e.g., subtracting from 12:02 AM), Excel shows a negative decimal unless you format the cell to display times, which may show ##### or a negative time depending on your settings.
Formatting the Result Correctly
After entering the formula, the result cell may show a decimal like 0.3715 instead of 8:55. To fix this:
- Select the cell (or range).
- Press
Ctrl+1to open Format Cells. - Choose Time under Category.
- Select a format like
1:30 PMor13:30. - Click OK.
If you want the result as a duration (e.g., 8:55) rather than a time of day, use the custom format [h]:mm to allow hours beyond 24.
Common Mistakes and How to Avoid Them
- Forgetting to format as time – The result appears as a decimal. Always apply a time format.
- Using
5instead ofTIME(0,5,0)–=A1-5subtracts 5 days, not 5 minutes. - Subtracting minutes as hours –
=A1-5/60subtracts 5 hours, not minutes. Use5/1440. - Text-formatted times – If the source cell is text, the formula returns
#VALUE!. Convert text to time usingTIMEVALUEor re-enter as real time. - Negative times – When subtracting from times before 12:05 AM, the result is negative. Excel displays
#####by default. Use the 1904 date system (File > Options > Advanced > When calculating this workbook) to show negative times, or adjust the formula to wrap withMODif you want a 24-hour wrap-around.
Subtracting Minutes from a Range or List
If you have many times in column A, you can copy the formula down. For example, in B2 enter =A2 - TIME(0,5,0) and drag the fill handle. To subtract a variable number of minutes from a cell (e.g., minutes in C2), use =A2 - TIME(0,C2,0). For a duration greater than 24 hours, use =A2 - C2/1440 where C2 contains the total minutes.
Using Subtraction with Other Time Functions
The same principle applies when subtracting seconds or hours. For instance, to subtract 5 seconds: =A1 - TIME(0,0,5). To subtract 5 hours: =A1 - TIME(5,0,0). You can also combine with NOW() to get the current time minus 5 minutes: =NOW() - TIME(0,5,0). This updates each time the worksheet recalculates.
Frequently Asked Questions
How do I subtract 5 minutes from a time in Excel without a formula?
You can use Paste Special: enter 5 in a cell, copy it, select the time cells, right-click > Paste Special > Subtract. However, this only works if the time cells are numeric; it also permanently changes the values. For a non-destructive method, use the formula approach.
Why does my formula return a decimal like 0.3715?
Because Excel stores times as fractions of a day. The decimal is the correct numeric result. To display it as a time, apply a time format to the cell. If you don’t see a change, the cell may be formatted as General or Number.
Can I subtract 5 minutes from a time that includes a date?
Yes. If the cell contains a full date and time (e.g., 2025-03-15 9:00), the formula =A1 - TIME(0,5,0) subtracts 5 minutes from the time part, leaving the date unchanged unless the subtraction crosses midnight, in which case the date rolls back automatically.
What if the result shows #####?
This usually means the column is too narrow to display the time, or the result is a negative time. Widen the column. If it’s negative, enable the 1904 date system under File > Options > Advanced, or adjust the formula to add 1 day if negative: =IF(A1 - TIME(0,5,0) < 0, A1 - TIME(0,5,0) + 1, A1 - TIME(0,5,0)).
Is there a way to subtract 5 minutes from every time in a column using a single formula?
Yes, if you have a dynamic array version of Excel (365 or 2021), you can enter =A2:A100 - TIME(0,5,0) in a single cell; it will spill results down. For older versions, use the fill handle to copy the formula to each row.
Does subtracting 5 minutes work the same in Google Sheets?
Yes, Google Sheets uses the same time serial number system. The formula =A1 - TIME(0,5,0) works identically. Formatting options are similar under Format > Number > Time.
Related
Formula used
in t | Start time (hours:minutes) | hh:mm | 9:00 | 0 out result | Time minus 5 minutes | time | (HOUR(t)*60 + MINUTE(t) - 5) / 1440 | 0
Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.