ThisCalculator

Everyday

Excel Subtract 15 Minutes from Time: Quick Guide

Updated · Standard formula, shown below

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 15 minutes from a time in Excel, use the formula =A1-TIME(0,15,0) where A1 contains your original time. This returns the time 15 minutes earlier, and you can copy it down for multiple cells. The formula works with both time-only values and full date-time stamps, automatically handling the day rollover if the result crosses midnight.

Why TIME(0,15,0) Is the Standard Method

Excel stores times as decimal fractions of a day, so 15 minutes equals 15/1440 of a day (since 24 hours × 60 minutes = 1440 minutes). The TIME function converts hours, minutes, and seconds into that decimal, making TIME(0,15,0) the clearest and least error-prone way to express a 15-minute interval. You could also write =A1-15/1440 or =A1-TIMEVALUE("0:15"), but TIME is more readable and avoids manual fraction mistakes.

Step-by-Step: Subtract 15 Minutes from a Single Cell

  1. Ensure your time is in a cell formatted as a time (e.g., 9:30 AM or 09:30). If it’s text, Excel may not calculate correctly.
  2. In an empty cell, type =A1-TIME(0,15,0) (replace A1 with your actual cell reference).
  3. Press Enter. The result appears as a time, but if it shows a decimal like 0.3854, right-click the cell, choose Format Cells, and select a time format.
  4. To apply to a column, drag the fill handle down from the corner of the formula cell.

For example, if A1 contains 10:00 AM, the formula returns 9:45 AM. If A1 contains 12:05 AM (just past midnight), the result is 11:50 PM of the previous day—Excel handles this automatically when the cell is formatted as a date-time.

Handling Full Date-Time Values

When your cell includes both a date and a time (e.g., 2025-03-15 14:30), the same formula works because the date is the integer part and the time is the fractional part. Subtracting TIME(0,15,0) reduces the fraction, and if the time goes below zero, Excel borrows from the date integer. This means you don’t need separate formulas for date-only vs. time-only cells—just ensure the cell is formatted to show both if you want the date visible.

Common Mistakes and How to Avoid Them

  • Using 15 instead of TIME(0,15,0): Writing =A1-15 subtracts 15 days, not 15 minutes, because Excel interprets a bare number as days.
  • Forgetting to format the result: If the answer appears as a decimal like 0.3854, it’s still correct—just change the number format to Time.
  • Text-formatted times: If your source cell has an apostrophe or is left-aligned, use =TIMEVALUE(A1)-TIME(0,15,0) after converting the text to a real time.
  • Negative results: If the original time is before 12:15 AM, the result becomes a negative fraction. Excel may display ###### or a negative time depending on your version. To show negative times, go to File > Options > Advanced and check “Use 1904 date system” (this is a global setting, so use with caution).

Alternatives to TIME(0,15,0)

If you prefer not to use the TIME function, these produce identical results:

  • =A1-15/1440 — divides 15 by the minutes in a day.
  • =A1-TIMEVALUE("0:15") — converts the text string “0:15” to a time value.
  • =A1-0.0104167 — the decimal equivalent of 15 minutes (15/1440 ≈ 0.0104167). This is less readable but works.

For subtracting a variable number of minutes, put the minutes in a cell (say B1) and use =A1-TIME(0,B1,0). This lets you change the interval without editing the formula.

Subtracting 15 Minutes Across a Range or with Conditions

If you need to subtract 15 minutes from every time in a column, enter the formula in the first row and drag down. For conditional subtraction (e.g., only if the time is after a certain hour), use =IF(A1>TIME(8,0,0), A1-TIME(0,15,0), A1). This checks if A1 is after 8:00 AM; if true, it subtracts 15 minutes, otherwise it returns the original time. For large datasets, you can also use Paste Special: enter 0:15 in a cell, copy it, select your time range, right-click > Paste Special > Subtract. This avoids formulas entirely and overwrites the original values.

Formatting the Result Correctly

After subtracting, the result may inherit the format of the original cell, but if you’re working with mixed formats, set the result column to a time format. Right-click > Format Cells > Time, and choose a style like 1:30 PM or 13:30. If you need to show the date as well, select a date-time format under Date or Custom. For durations longer than 24 hours, use the custom format [h]:mm to avoid Excel resetting to 0 at midnight.

Frequently Asked Questions

How do I subtract 15 minutes from a time in Excel without a formula?

You can use Paste Special. Type 0:15 in any empty cell, copy it, select the cells with your times, right-click > Paste Special > Subtract, then press OK. This directly reduces each time by 15 minutes without leaving formulas behind.

Why does my result show as a decimal number?

Excel stores times as fractions of a day, so a result like 0.3854 is actually 9:15 AM. To display it as a time, select the cell, press Ctrl+1, choose Time, and pick a format. The underlying value is correct—only the display format needs changing.

What if the time is before 12:15 AM? Will it go negative?

Yes, subtracting 15 minutes from 12:05 AM gives 11:50 PM of the previous day, which Excel represents as a negative fraction. By default, Excel may show ###### or an error. To see the correct previous-day time, format the cell as a date-time, or enable the 1904 date system under Options > Advanced if you need negative times to display.

Can I subtract 15 minutes from a column of times at once?

Yes. Enter the formula in the first cell, then double-click the fill handle (the small square at the bottom-right of the cell) to copy it down the entire column. Alternatively, use the Paste Special method described above to change all values in place.

Does this work with times that include seconds?

Yes. The TIME function accepts seconds as the third argument, so =A1-TIME(0,15,0) subtracts exactly 15 minutes and leaves seconds unchanged. If you need to subtract 15 minutes and 30 seconds, use =A1-TIME(0,15,30).

Related

Formula used

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.