ThisCalculator

Everyday

Subtract Time Values in Excel in Minutes

Updated · Standard formula, shown below

To subtract time values in Excel and get the result in minutes, use the formula =(EndTime - StartTime) * 1440 and format the cell as a number. 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.

How Excel Stores Time

Excel treats time as a decimal fraction of a 24-hour day. For example, 6:00 AM is stored as 0.25, and 6:00 PM is stored as 0.75. This means that when you subtract two time values, you get a fraction of a day, not a number of minutes or hours. To get minutes, you multiply that fraction by 1440 (24 hours × 60 minutes).

This underlying representation is why the same subtraction can be displayed as a time (e.g., 8:00) or as a number (e.g., 0.3333), depending on the cell format. Understanding this is the key to avoiding the confusing results many users encounter.

Basic Subtraction Formula for Minutes

If you have a start time in cell A1 and an end time in cell B1, the simplest formula to get the elapsed minutes is:

= (B1 - A1) * 1440

Make sure the cell containing this formula is formatted as a number (not as a time). To do this, right-click the cell, choose Format Cells, select Number, and set the desired decimal places.

For example, if A1 contains 9:30 AM and B1 contains 11:15 AM, the formula returns 105, which is the correct number of minutes between those two times.

Handling Times That Cross Midnight

A common problem is when a shift starts at 10:00 PM and ends at 6:00 AM the next day. A simple subtraction like B1 - A1 gives a negative value because Excel does not automatically know the date changed. To fix this, use the MOD function:

= MOD(B1 - A1, 1) * 1440

The MOD function wraps the result around at 1 (one full day), effectively adding 24 hours when the result would be negative. This gives the correct elapsed minutes for overnight intervals.

Using TEXT to Format the Result

If you want the result to appear as a number but also include the word “minutes” in the same cell, you can use the TEXT function:

= TEXT(B1 - A1, "[m]") & " minutes"

The format code [m] displays the total elapsed minutes even if the total exceeds 60. Without the square brackets, Excel would show only the minutes component after subtracting whole hours. For example, 2 hours and 5 minutes would show as 5 without brackets, but as 125 with them.

Common Mistakes and How to Avoid Them

  • Forgetting to multiply by 1440: The result of a simple subtraction is a fraction of a day, not minutes. Always multiply by 1440 unless you want the result as a time value.
  • Formatting the result as a time: If you leave the cell formatted as time, a result of 105 minutes will display as 1:45 (1 hour 45 minutes). To see 105, apply a number format.
  • Using text that looks like time: If you type “9:30” as text (with an apostrophe before it), Excel cannot perform arithmetic on it. Ensure your time values are actual time entries, not text.
  • Ignoring date changes: For shifts crossing midnight, always use the MOD formula to avoid negative results.

Alternative: Using the HOUR and MINUTE Functions

Another approach is to extract hours and minutes separately and combine them:

= HOUR(B1 - A1) * 60 + MINUTE(B1 - A1)

This works for intervals under 24 hours, but it fails for longer durations because the HOUR function only returns the hour component (0-23), not the total hours. For example, a difference of 25 hours would give 1 hour, not 25. The multiplication by 1440 method is more robust for longer durations.

Frequently Asked Questions

Why does my subtraction show a negative time like ########?

This happens when the end time is earlier than the start time, meaning the interval crosses midnight. Use the MOD formula =MOD(B1-A1,1)*1440 to get the correct positive minutes.

How do I convert a time like 1:30 to 90 minutes?

If you have a time value in a cell, multiply it by 1440. For example, =A1*1440 where A1 contains 1:30 will return 90. This works because the time value is stored as a fraction of a day.

Can I subtract two times that are in different cells but formatted as text?

No, text values cannot be used in arithmetic operations. You need to convert them to real times first using the TIMEVALUE function, e.g., =TIMEVALUE(A1), then subtract.

What if my difference is more than 24 hours?

The simple subtraction works, but the result will be a fraction greater than 1. Multiplying by 1440 gives the total minutes correctly. For example, 30 hours becomes 1800 minutes. Just ensure the cell format is numeric, not date/time.

How do I show the result as “X hours Y minutes”?

Use the formula =INT((B1-A1)*1440/60) & " hours " & MOD((B1-A1)*1440,60) & " minutes". This divides total minutes by 60 to get whole hours, then uses MOD to get the remaining minutes.

Does the method work in Google Sheets too?

Yes, the same formulas work in Google Sheets because it also stores times as fractions of a day. The multiplication by 1440 and the MOD function behave identically.

Related

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.