Excel Subtract Time Difference in Minutes
To subtract time in Excel and get the difference in minutes, use the formula =(End_Time - Start_Time)*1440 and format the result as a number. This works because Excel stores times as fractions of a day, and multiplying by 1440 (the number of minutes in a day) converts the fraction to minutes.
How Excel Stores Time
Excel treats time as a decimal number where 1.0 equals 24 hours. So 6:00 AM is stored as 0.25, 12:00 PM as 0.5, and 6:00 PM as 0.75. When you subtract one time from another, you get a fraction of a day. For example, subtracting 9:00 AM from 10:30 AM gives 0.0625, which is 1.5 hours or 90 minutes. To get minutes, you multiply that fraction by 1440.
This is why simply subtracting two time cells and formatting the result as a number often shows a small decimal like 0.0625 rather than 90. The multiplication by 1440 is the key step that converts the fraction into whole minutes.
The Basic Formula
If your start time is in cell A1 and your end time is in cell B1, the formula is:
= (B1 - A1) * 1440
Here’s what each part does:
- B1 – A1 gives the time difference as a fraction of a day (e.g., 0.0625 for 90 minutes).
- * 1440 multiplies that fraction by the number of minutes in a day (24 hours × 60 minutes = 1440).
After entering the formula, format the cell as a number (not as time). You can do this by right-clicking the cell, choosing Format Cells, selecting Number, and setting the desired decimal places. If you leave it as a time format, you’ll see a time value instead of minutes.
Worked Example
Suppose you started work at 9:00 AM and finished at 5:30 PM. You want to know the total minutes worked.
- Enter
9:00in cell A1 and17:30in cell B1 (Excel accepts 17:30 for 5:30 PM). - In cell C1, enter
=(B1-A1)*1440. - Format C1 as a number. The result is 510 minutes.
Let’s verify: 9:00 AM to 5:30 PM is 8 hours and 30 minutes. 8 hours × 60 = 480 minutes, plus 30 minutes = 510 minutes. The formula gives exactly that.
Handling Time That Crosses Midnight
If your start time is in the evening and the end time is the next morning (e.g., 10:00 PM to 6:00 AM), a simple subtraction will produce a negative number because Excel doesn’t know the day changed. To fix this, use the MOD function:
= (MOD(B1 - A1, 1)) * 1440
This formula wraps around midnight. For 10:00 PM (22:00) to 6:00 AM, the difference is -0.6667, but MOD(-0.6667, 1) returns 0.3333, which is 8 hours. Multiply by 1440 to get 480 minutes. This works for any overnight shift as long as the total duration is less than 24 hours.
Using the DATEDIF Function (For Dates and Times)
If your cells contain both dates and times (e.g., 2025-03-15 9:00 and 2025-03-16 17:30), the simple subtraction still works because Excel treats dates as whole numbers and times as fractions. The formula =(B1-A1)*1440 will give the correct total minutes across days. However, if you want to extract only the minutes component (ignoring hours), you can use:
=MINUTE(B1 - A1)
But note that this returns the minute part (0-59), not the total minutes. For total minutes, always multiply by 1440.
Common Mistakes and How to Avoid Them
- Forgetting to multiply by 1440 – Without it, you get a fraction of a day, not minutes.
- Formatting as time – If the result cell is formatted as time, you’ll see a time value (e.g., 8:30) instead of 510. Change the format to Number.
- Negative results for overnight times – Use MOD or add 1 to the result if the end time is earlier than the start time.
- Using text instead of time values – Ensure your cells contain real time values, not text. You can test by checking if the cell is right-aligned by default. To convert text to time, use
=TIMEVALUE(A1). - Rounding errors – Excel may show a long decimal like 509.9999 due to floating-point precision. You can round with
=ROUND((B1-A1)*1440, 0)to get whole minutes.
When to Use This Formula
This formula is useful for tracking work hours, calculating call durations, measuring process times, or any scenario where you need the elapsed time in minutes. It’s especially handy when you have many rows of start and end times and need to compute totals or averages.
For example, if you have a list of tasks with start and end times, you can add a column with this formula and then sum it to get the total minutes spent. You can also convert minutes to hours by dividing by 60 if needed.
Frequently Asked Questions
Why does my formula return a decimal like 0.0625 instead of minutes?
Because you haven’t multiplied by 1440. The raw difference is a fraction of a day. For example, 1.5 hours is 0.0625 of a day. Multiplying by 1440 converts that to 90 minutes.
How do I subtract time and show the result as hours and minutes?
Use the same subtraction but format the result as [h]:mm. For example, =B1-A1 formatted as [h]:mm will show total hours and minutes, even if over 24 hours. To get minutes only, multiply by 1440.
What if my time crosses midnight?
Use =MOD(B1-A1,1)*1440 to handle overnight differences. This works as long as the total duration is less than 24 hours. If it’s longer, include dates in your cells.
Can I subtract time in minutes without using a formula?
You can use the Paste Special feature to subtract a constant number of minutes from a time, but for differences between two cells, a formula is the standard method.
Why does my result show as a time like 8:30 instead of 510?
Because the cell is formatted as time. Right-click the cell, select Format Cells, and choose Number. Then it will display 510.
How do I subtract 30 minutes from a time in Excel?
To subtract a specific number of minutes from a time, use =A1 - (30/1440) where A1 is the time. Since 30 minutes is 30/1440 of a day, this subtracts exactly 30 minutes.
Related
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.