If the start time is in A2 and the end time is in B2, enter =(B2-A2)*1440 to calculate the minutes between them. Format the result as a number; multiply by 1,440 because Excel stores one day as 1 and one minute as 1/1,440 of a day.
The basic minutes-between-times formula
For example, if A2 contains 9:15 AM and B2 contains 10:05 AM, the formula returns 50. It works by subtracting the start time from the end time to get an elapsed portion of a day, then converting that portion into minutes. Enter the formula in another cell, such as C2, and press Enter. To calculate more rows, drag the fill handle at the lower-right corner of C2 down the column; Excel adjusts the row references automatically.
The formula assumes both cells contain Excel-recognized time values and that the end time is later than the start time on the same day. If you want a decimal number of minutes, this is usually the most direct approach. For example, a result of 50.5 means 50 minutes and 30 seconds, not 50 minutes and 50 seconds.
Before using the result in further calculations, check the result cell’s number format. A time format can make a correct result look like a clock time rather than a count of minutes. Select the result cell and choose Home > Number Format, then select Number or General. You can increase or decrease the displayed decimal places from the Home tab if needed.
Why Excel multiplies by 1,440
Excel represents dates and times as serial numbers. The whole-number portion represents days, while the fractional portion represents the time within a day. In that system, noon is 0.5 and 6:00 AM is 0.25. Subtracting two times therefore returns a fraction of a day, not a number of minutes.
There are 24 hours in a day and 60 minutes in an hour, so 24 × 60 = 1,440 minutes per day. Multiplying the difference by 1,440 converts the day fraction to minutes. The same logic explains related conversions: multiply an elapsed-day value by 24 for hours, or by 86,400 for seconds. The result is a numeric quantity, which makes it useful for totals, averages, comparisons, and charts.
It is also possible to display an elapsed time as hours and minutes rather than as a number of minutes. That is a different presentation, not a different elapsed-time calculation. The distinction matters when a worksheet needs to show a duration such as 2:30, but another calculation needs the value 150.
Set up the cells and check the inputs
For a simple time log, use one column for the start, another for the end, and a third for elapsed minutes. Labels such as Start, End, and Minutes make it easier to interpret and maintain the sheet. Enter times using a format Excel recognizes, such as 9:15 AM or 13:15. Excel may display the entry in a different style according to the cell’s number format, but that does not necessarily change the underlying value.
- Enter the starting time in A2 and the ending time in B2.
- Enter
=(B2-A2)*1440in C2. - Set C2 to General or Number so the answer appears as a minute count.
- Copy the formula down for other records and review a few rows against the source times.
If Excel treats a time entry as text, subtraction may return a #VALUE! error or behave unexpectedly. A common clue is that the value does not respond to number formatting as a time should. Imported data can contain leading spaces, unusual separators, or text labels mixed into the column. Convert or clean those inputs before calculating; do not assume that a string that looks like a time is already a numeric Excel time.
Calculate a shift that crosses midnight
The basic subtraction produces a negative difference when a shift starts before midnight and ends after midnight, if both cells contain only clock times. For example, 10:45 PM to 2:15 AM crosses into the next day. If every entry is known to be a same-day or overnight interval and the end time earlier on the clock means “next day,” use MOD to wrap the difference into a 24-hour cycle.
With the start time in A2 and the end time in B2, use this formula for an interval that may cross midnight:
=MOD(B2-A2,1)*1440
For A2 equal to 10:45 PM and B2 equal to 2:15 AM, the result is 210 minutes: three hours and 30 minutes. MOD returns the remainder within one day, so the negative clock-time difference becomes the intended positive overnight interval.
This shortcut has an important limitation: identical start and end times return zero, not 1,440 minutes. It also treats any end time earlier than the start as an overnight finish. That is appropriate for many shift logs, but it can conceal an incorrect entry. If the times belong to dated records, store the dates as well and subtract the full date-time values instead.
Use full dates for multi-day intervals
When an interval can last longer than a day, or when records have actual start and end dates, enter both the date and time in each cell. For example, A2 might contain November 2, 2026 at 10:45 PM, while B2 contains November 3, 2026 at 2:15 AM. The underlying values include both the date and the time, so direct subtraction correctly accounts for the day change.
With the full start date and time in A2 and the full end date and time in B2, use:
=(B2-A2)*1440
That example returns 210 minutes. The same formula works for intervals spanning several days; it does not wrap the result at midnight. If B2 is earlier than A2 as a complete date-time value, however, the result is negative. That usually signals reversed entries or a data issue rather than a reason to force the result positive.
For a duration displayed as hours and minutes instead of total minutes, calculate the date-time difference without converting it to minutes, then apply a duration format. Select the result cell, open Format Cells with Ctrl+1 on Windows, choose Custom, and enter [h]:mm as the format. Square brackets around h allow the displayed hours to continue past 24. Without them, a duration of 27 hours may display as 3:00 because ordinary clock formatting cycles at 24 hours. On Excel for Mac, Command+1 opens Format Cells; the dialog’s available choices may be arranged differently.
Round minutes and handle seconds
Time values can include seconds even when the cells display only hours and minutes. If the start and end differ by 49 minutes and 50 seconds, the exact result is 49.8333 minutes. Decide whether the worksheet should preserve fractional minutes or report whole minutes. Rounding is a reporting choice: rounding to the nearest minute can differ from always rounding down or up.
With a start time in A2 and an end time in B2, use this formula to round elapsed minutes to the nearest whole minute:
=ROUND((B2-A2)*1440,0)
If A2 is 9:15:20 AM and B2 is 10:05:10 AM, the elapsed time is 49 minutes and 50 seconds, or about 49.8333 minutes. The formula returns 50. If the requirement is to retain seconds as a count, use a seconds conversion instead.
With the start time in A2 and the end time in B2, this formula returns elapsed seconds for a same-day interval:
=(B2-A2)*86400
For a start of 9:15:10 AM and an end of 10:05:40 AM, the result is 3,030 seconds, which is 50 minutes and 30 seconds. As with minutes, use full date-time values for intervals that cross midnight, or adapt the calculation for a known overnight shift. Avoid rounding intermediate values if later calculations need the original precision.
Choose the right calculation for the situation
The best formula depends on what the cells mean, not just on how their contents look. Clock times alone do not tell Excel which date an entry belongs to. Choose the calculation based on whether the interval is same-day, overnight, or based on complete date-time records:
- Same-day clock times: subtract the start from the end and multiply by 1,440.
- Known overnight shift with clock times only: use MOD to wrap a negative clock difference, while checking for mistaken entries.
- Actual dates and times, including multi-day intervals: subtract the full date-time values and convert to minutes.
- Display as a duration: keep the elapsed-time value and format it as
[h]:mmrather than converting it to a minute count.
A frequent misconception is that changing a cell’s format changes the stored calculation. Formatting changes how a value appears, not the underlying elapsed time. For instance, formatting a value as a clock time does not convert a decimal number of minutes into a useful duration display. Use a numeric format for minute counts and a duration format when showing hours and minutes.
Common errors and how to diagnose them
If the result is a small decimal such as 0.0347, the subtraction may be correct but the formula may be missing the conversion factor. A difference of 0.0347 days is approximately 50 minutes. Multiply the difference by 1,440 when the intended answer is a number of minutes.
If the result appears as a time of day, change the result cell to General or Number. If it shows #####, widen the column first; Excel uses that display when a value does not fit in some formats. A negative time displayed with hash marks can also point to a reversed interval or an unsuitable time format. Check the inputs and decide whether the interval is truly overnight before applying MOD.
If the answer is off by a small fraction, the input may include seconds or the formula may be displaying more precision than expected. If the difference is a whole number of hours, check whether one entry is AM and the other PM. If a formula returns an error, confirm that both cells contain valid Excel time values rather than text. For records where an ending time can be earlier for reasons other than an overnight shift, use dates or add a separate validation check rather than automatically wrapping every result.
Frequently Asked Questions
How do I calculate minutes between two times in Excel?
With the start time in A2 and the end time in B2, enter =(B2-A2)*1440. Format the formula cell as General or Number to see the result as a count of minutes.
How do I calculate time between two times when the shift passes midnight?
For clock times only, with the start in A2 and end in B2, use =MOD(B2-A2,1)*1440 when an earlier end time means the following day. Use complete date-time values instead if the dates are known or the interval could span more than one day.
How can I show the answer as hours and minutes instead of total minutes?
Subtract the start from the end without multiplying by 1,440, then apply the custom number format [h]:mm. The brackets let the hour total exceed 24; a format such as h:mm can cycle back to zero after a full day.
Why is Excel showing a decimal instead of minutes?
Excel’s direct time difference is a fraction of a day. Multiply it by 1,440 to convert that fraction into minutes. Also check that the result cell is formatted as General or Number rather than as a time.
How do I round elapsed minutes to a whole number?
With the start in A2 and end in B2, use =ROUND((B2-A2)*1440,0) to round to the nearest whole minute. Keep the unrounded result if seconds or fractional minutes matter to later calculations.
Related
- Excel Weekday Text: Show Dates as Day Names
- Excel WEEKDAY: Return a Day Name from a Date
- Excel NOW as Text: Show the Current Date and Time
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Sum a Row Based on a Column Header with SUMIFTo sum values across a row when their column headers match a label, use SUMIF: with headers in…
- Calculate Age in Excel Using Date of Birth with DATEDIFTo calculate age in Excel from a date of birth, put the birth date in A2 and enter…
- Excel Time Between Two Timestamps: Formulas and TipsIf the start timestamp is in A2 and the end timestamp is in B2, enter =B2-A2 to calculate…
- Named Ranges in XLOOKUP: How to Use Them in ExcelTo use a named range in XLOOKUP, replace a cell reference such as A2:A6 with the range’s name…
- Round Excel Without Decimals: Formula or Formatting?To round a value in Excel to the nearest whole number, use =ROUND(A1,0) when the value is in…
- INDEX MATCH With Two MATCH Functions: Practical ExamplesYes: the standard two-way lookup uses INDEX with one MATCH to find a row and a second MATCH…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.