ThisCalculator

Everyday

Excel Subtract Date and Time to Get Minutes

Updated · Standard formula, shown below

Average pace 5.00 min/km
Average speed
12.00 km/h
Result
Finish time
50 min
You entered
Distance
10 km
You entered

To subtract a start date and time from an end date and time in Excel and get the result in minutes, use the formula =(End_Cell - Start_Cell)*1440 and format the result as a number. This works because Excel stores dates and times as serial numbers where 1 equals one day, so multiplying the difference by 1440 (the number of minutes in a day) converts it to minutes.

Formula

The core formula is:

minutes = (end_datetime - start_datetime) * 1440

Where:

  • end_datetime is a cell containing the later date and time (e.g., B2).
  • start_datetime is a cell containing the earlier date and time (e.g., A2).
  • 1440 is the number of minutes in one day (24 hours × 60 minutes).

Excel internally represents every date and time as a decimal number. The integer part is the number of days since January 0, 1900 (or 1904 on some Mac versions), and the fractional part is the time of day as a fraction of 24 hours. For example, 6:00 AM is 0.25 because 6/24 = 0.25. When you subtract two datetime values, you get the difference in days, which can be a decimal. Multiplying by 1440 turns that day-based difference into minutes.

Worked Example

Suppose cell A2 contains 2024-01-15 9:30:00 AM and cell B2 contains 2024-01-15 11:45:00 AM.

  1. Subtract the start from the end: =B2-A2 gives 0.09375 (which is 2 hours and 15 minutes as a fraction of a day).
  2. Multiply by 1440: =0.09375*1440 = 135.

So the result is 135 minutes. This matches the default example in the calculator above: a finish time of 50 minutes over 10 km gives an average pace of 5.00 min/km and a speed of 12.00 km/h.

When to Use This

You need this formula whenever you track start and end timestamps and want the elapsed time in minutes rather than in days, hours, or seconds. Common scenarios include:

  • Calculating call durations from a call log with start and end times.
  • Computing processing times for tasks or orders in a spreadsheet.
  • Measuring time spent on projects or shifts.
  • Converting time differences for billing or payroll (though check local regulations).

The formula works for any pair of valid Excel date/time values, including those that span midnight or multiple days, because the underlying serial-number system handles the arithmetic naturally.

How to Read the Result

After entering the formula, the cell may display a time format (like 2:15) instead of a number. To see minutes as an integer, apply a number format:

  1. Select the cell with the formula.
  2. Press Ctrl+1 (Windows) or Cmd+1 (Mac) to open Format Cells.
  3. Choose Number and set decimal places to 0 or 2 as desired.

The result is the total number of minutes between the two moments. For example, 135 minutes is 2 hours and 15 minutes. If you need hours and minutes separately, you can use =INT(minutes/60) for hours and =MOD(minutes,60) for remaining minutes.

Common Mistakes and Pitfalls

  • Forgetting to multiply by 1440: Without it, you get the difference in days (e.g., 0.09375), which is not what you want.
  • Formatting the result as a time: If the cell is formatted as time, 135 will display as 3:00 (because 135/24 = 5.625 days, which is 3:00 AM on day 6). Always format as a number.
  • Using text instead of real dates: If your dates are typed as text (e.g., “Jan 15, 2024”), subtraction won’t work. Use DATEVALUE or ensure the cells are actual date/time values.
  • Mixing time zones: Excel doesn’t handle time zones automatically. If your timestamps are in different zones, convert them to a common time zone first.
  • Negative differences: If the start time is later than the end time, the result will be negative. This is correct for data entry errors, but you may want to use =ABS(...) to get the absolute difference.

Alternatives and Related Formulas

Depending on your needs, you might use other functions:

  • DATEDIF for whole days, months, or years, but it cannot return minutes.
  • TEXT to format the difference as a string, e.g., =TEXT(B2-A2,"h:mm") gives hours and minutes but as text.
  • MOD to handle times that cross midnight if you only have times (not full dates). For example, =MOD(B2-A2,1)*1440 works for times without dates.
  • NETWORKDAYS for business days, but that’s for days, not minutes.

For most cases, the simple multiplication by 1440 is the most direct and reliable method.

Limitations and Edge Cases

The formula assumes your date/time values are stored as Excel serial numbers. If you import data from other systems, they might come in as text or with different formats. In such cases, use DATEVALUE and TIMEVALUE to convert them first. Also, Excel’s date system starts at January 1, 1900, so dates before that are not supported. For very large time differences (e.g., years), the result in minutes will be very large but still accurate.

Another edge case: if you only have times (no dates) and the end time is on the next day (e.g., start 11:00 PM, end 1:00 AM), a simple subtraction yields a negative number. Use =MOD(B2-A2,1)*1440 to get the positive elapsed minutes (120 minutes in this example).

Frequently Asked Questions

Why does my formula return a time like 3:00 instead of a number?

This is a formatting issue. The cell is formatted as a time, so Excel displays the numeric result as a time of day. Change the number format to Number or General to see the minute count.

Can I subtract only times, not full dates?

Yes, if your cells contain only times (e.g., 9:30:00 AM), the subtraction works the same way, but you need to handle crossing midnight. Use =MOD(B2-A2,1)*1440 to get the correct positive minutes.

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

After getting the total minutes, you can compute hours with =INT(minutes/60) and remaining minutes with =MOD(minutes,60). Or format the difference as [h]:mm to show total hours and minutes (e.g., 2:15).

What if my dates are in text format?

Text dates won’t subtract correctly. Convert them using DATEVALUE and TIMEVALUE functions, or use the Text to Columns wizard to convert them to real dates.

Does this work across different days, like from Monday to Tuesday?

Yes, as long as both cells contain full date and time values, the subtraction gives the total elapsed time in days, which multiplied by 1440 gives the correct total minutes.

Why is my result negative?

If the start time is later than the end time, the difference is negative. This often happens when you forget to include dates and the end time is on the next day. Use MOD for times without dates, or ensure your end datetime is greater than the start datetime.

Related

Formula used
in t | Finish time | min | 50 | 0
in d | Distance | km | 10 | 0
out pace | Average pace | min/km | t / d | 2
out speed | Average speed | km/h | d / (t / 60) | 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.