Excel Subtract Time in Minutes: Quick Formula
To subtract time in minutes in Excel, use the formula =End_Time - Start_Time and multiply the result by 1440 (the number of minutes in a day). For example, =(B2-A2)*1440 returns the elapsed minutes between the time in A2 and the time in B2.
How Excel Stores Time
Excel treats time as a fraction of a 24-hour day. 12:00 PM is stored as 0.5, 6:00 AM as 0.25, and so on. Because of this, when you subtract two time values, you get a decimal fraction of a day. To convert that fraction into minutes, you multiply by 24 (hours in a day) and then by 60 (minutes in an hour), which is the same as multiplying by 1440.
Formula for Subtracting Time in Minutes
The core formula is:
= (End_Time - Start_Time) * 1440
Where:
- End_Time – the later time (cell reference or a time value)
- Start_Time – the earlier time
- 1440 – the number of minutes in a day (24 × 60)
If you type times directly into the formula, enclose them in quotes: =("6:30 PM"-"5:00 PM")*1440. This returns 90 minutes.
Worked Example with Default Values
Suppose cell A2 contains 9:00 AM and cell B2 contains 5:30 PM. The formula =(B2-A2)*1440 works like this:
- Subtract the start time from the end time: 5:30 PM – 9:00 AM = 0.3541667 (as a fraction of a day).
- Multiply by 1440: 0.3541667 × 1440 = 510 minutes.
So the result is 510 minutes. That is 8.5 hours, which matches 9:00 AM to 5:30 PM.
Formatting the Result
After entering the formula, the cell may show a time value (like 8:30) instead of a number. To fix that:
- Select the cell with the formula.
- Press Ctrl+1 (or right-click → Format Cells).
- Choose Number and set decimal places to 0 or 2.
If you want the result as a time duration (e.g., 8:30 for 8 hours 30 minutes), use the format [h]:mm instead of multiplying by 1440. The square brackets allow hours over 24.
Handling Times That Cross Midnight
If the end time is on the next day (e.g., start 10:00 PM, end 2:00 AM), a simple subtraction gives a negative value. Use the MOD function to handle the wrap-around:
= MOD(End_Time - Start_Time, 1) * 1440
For example, =MOD(("2:00 AM"-"10:00 PM"),1)*1440 returns 240 minutes (4 hours). The MOD function adds 1 if the difference is negative, effectively adding a full day.
Common Mistakes and Pitfalls
- Forgetting to multiply by 1440 – Without it, you get a fraction of a day, not minutes.
- Using text instead of time values – If your cells contain text like “9:00 AM” as a string, subtraction won’t work. Convert text to time using
TIMEVALUE()or re-enter as proper times. - Cell formatted as time – The result may display as a clock time (e.g., 8:30) even though the underlying value is 510. Change the format to Number.
- Negative results without MOD – Crossing midnight produces negative values unless you use MOD or add 1.
Alternatives: Using DATEDIF or TEXT
For more complex time differences, you can use:
DATEDIF(start, end, "d")– days only, not for minutes.TEXT(end-start, "[m]")– returns minutes as text (e.g., “510”). This is useful when you want a text result, but it is not a number for further calculations.HOUR()andMINUTE()– extract hours and minutes separately:=HOUR(B2-A2)*60 + MINUTE(B2-A2)gives the same minutes but only works if the difference is less than 24 hours.
Frequently Asked Questions
How do I subtract 30 minutes from a time in Excel?
Use =A1 - TIME(0,30,0) or =A1 - 30/1440. Both subtract 30 minutes from the time in A1. The TIME function is clearer: TIME(0,30,0) represents 30 minutes.
Why is my time subtraction result showing as a time like 8:30?
That is because the cell is formatted as a time. The underlying value is correct (a fraction of a day). Change the cell format to Number to see minutes (e.g., 510).
Can I subtract time across midnight without errors?
Yes, use =MOD(end-start,1)*1440. This handles the wrap-around by adding 1 when the difference is negative.
How do I subtract time in Excel when the result is over 24 hours?
If you want total minutes, the *1440 method works fine (e.g., 1500 minutes for 25 hours). If you want a time display, use the format [h]:mm to show hours over 24.
Does Excel subtract time as a decimal or as a fraction?
Excel subtracts times as fractions of a day. For example, 6 hours is 0.25. To get minutes, multiply by 1440. This is consistent across all time calculations.
What if my times are in text format?
Convert them using TIMEVALUE() in the formula: =(TIMEVALUE(B2)-TIMEVALUE(A2))*1440. Or use Data → Text to Columns to convert text to time.
Related
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.