ThisCalculator

Everyday

Excel Subtract Times to Get Minutes

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 times in Excel and get the result in minutes, use the formula =(B2-A2)*1440, where A2 is the start time and B2 is the end time. This works because Excel stores times as fractions of a day, so multiplying the difference by 1440 (the number of minutes in a day) converts it to minutes. This article explains the method, common pitfalls, and how to handle cases that cross midnight.

Formula

The core formula is minutes = (end_time - start_time) * 1440. In Excel, time is stored as a decimal fraction of a 24-hour day. For example, 6:00 AM is 0.25, and 12:00 PM is 0.5. The difference between two times is therefore a fraction of a day. Multiplying that fraction by 1440 (24 hours × 60 minutes) converts it to minutes. If you want hours instead, multiply by 24.

Variables:

  • start_time – the earlier time, as a time value (e.g., 9:00 AM).
  • end_time – the later time, as a time value (e.g., 5:30 PM).
  • 1440 – constant: minutes in a day (24 × 60).

Worked Example

Using the default values from the calculator: start at 9:00, end at 17:30 (5:30 PM).

  1. Subtract the start from the end: 17:30 – 9:00 = 8:30 (8.5 hours).
  2. Excel represents 8.5 hours as 8.5/24 = 0.3541667 (a fraction of a day).
  3. Multiply by 1440: 0.3541667 × 1440 = 510 minutes.

The result is 510 minutes, which is 8 hours and 30 minutes. The calculator shows 510 minutes as the headline answer and 8.50 hours as a secondary output.

How Excel Handles Time

Excel treats time as a number between 0 and 1, where 0 is 12:00 AM and 0.99999 is 11:59 PM. This is why you can simply subtract two time cells. However, if you subtract a later time from an earlier one (e.g., 9:00 PM minus 5:00 PM), you get a negative fraction, which displays as ######## unless you change the cell format. To avoid this, use the MOD function: =MOD(end - start, 1) to get a positive result even when crossing midnight.

Handling Times That Cross Midnight

If a shift starts at 10:00 PM and ends at 6:00 AM the next day, a simple subtraction gives a negative value. The fix is to use =MOD(B2-A2,1) which adds 1 (a full day) if the result is negative. Then multiply by 1440 to get minutes. For example: =MOD(6:00 AM - 10:00 PM, 1) * 1440 = MOD(-0.6667, 1) = 0.3333, times 1440 = 480 minutes (8 hours).

Common Mistakes and How to Avoid Them

  • Not formatting cells as time – If you type 9:00 without a space, Excel may interpret it as text. Always use the time format (e.g., 9:00 AM) or the 24-hour format (e.g., 17:30).
  • Forgetting to multiply by 1440 – The difference alone is a fraction of a day, not minutes. Many users see 0.354 and think it’s minutes, but it’s actually 8.5 hours.
  • Using decimal times – If you enter 9.5 for 9:30, the subtraction gives a decimal, not a time. Use time format or convert manually.
  • Rounding errors – When you multiply by 1440, you may get a decimal like 510.0000001. Use ROUND to clean it up: =ROUND((B2-A2)*1440,0).

Formatting the Result

After calculating minutes, you may want to display them as a number or as a duration. If you want the result to appear as “8:30” (hours:minutes), keep the cell format as [h]:mm. To do that, right-click the cell, choose Format Cells, select Custom, and type [h]:mm. This shows total hours even if they exceed 24. If you want just minutes as a number, format as General or Number.

Alternatives to the Basic Formula

For more complex scenarios, you can use the TEXT function to extract parts: =TEXT(B2-A2,"h")*60 + TEXT(B2-A2,"m") but this is less reliable. A better alternative is to use HOUR and MINUTE functions: =HOUR(B2-A2)*60 + MINUTE(B2-A2). This works only if the difference is less than 24 hours. For durations over 24 hours, stick with the multiplication method.

Frequently Asked Questions

Why does my formula return a time like 8:30 AM instead of minutes?

That happens when you don’t multiply by 1440. The subtraction alone gives a fraction of a day, which Excel displays as a time. To get minutes, you must multiply by 1440. If you see a time, change the formula to include *1440 and format the cell as a number.

How do I subtract times that cross midnight?

Use the MOD function: =MOD(B2-A2,1)*1440. This adds a full day if the result is negative. For example, from 10:00 PM to 6:00 AM gives 480 minutes.

What if my times are in text format?

Convert text to time using TIMEVALUE. For example, if A1 contains “9:00 AM” as text, use =TIMEVALUE(A1) to turn it into a time value. Then subtract normally.

Can I get the result in hours and minutes instead of just minutes?

Yes. Divide the minutes by 60 to get hours, or use the formula =(B2-A2)*24 for hours as a decimal. For example, 510 minutes is 8.5 hours. To show it as 8:30, format the cell as [h]:mm.

Why does Excel show ######## instead of a number?

That usually means the column is too narrow to display the time or number. Widen the column, or change the cell format to a number. If the result is negative, it may also show ########; use MOD to fix that.

How do I subtract multiple time ranges to get total minutes?

Sum the individual differences first, then multiply by 1440. For example, =SUM(C2:C10)*1440 where C column contains the time differences. Make sure each difference is positive, or use MOD for each.

Related

Formula used
in t | Start time (hh:mm) | - | 9:00 | 0
in t | End time (hh:mm) | - | 17:30 | 0
out minutes | Total minutes | min | (end - start) * 1440 | 0
out hours | Total hours | h | (end - start) * 24 | 2

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.