For the table below, I would like to write a formula that will tell me the difference in minutes between the first row and the next, going through the table. For example, how much time was between C2 and C3, then between C3 and C4.

I haven't found a formula that shows the difference between the rows that are in the same column.

For the table below, I would like to write a formula that will tell me the difference in minutes between the first row and the next, going through the table. For example, how much time was between C2 and C3, then between C3 and C4.

I haven't found a formula that shows the difference between the rows that are in the same column.

• excel
• excel-formula

• In order to answer this fully, we need more info. Specially, is the data in column C a DateTime serial number including the date (formatted to display just the time), or a DateTime serial number containing just the time component, or a string. – chris neilsen Commented Feb 8 at 5:34
• Note that all answers received so far assume it's a full DateTime serial number – chris neilsen Commented Feb 8 at 5:35

5 Answers 5

If these are just times, not datetimes, then you could take care of the transition from one day to the next (which would otherwise produce a negative result) by

=MOD(C3 - C2,1) * 1440

assuming time differences are less than 24 hours

or as a spill formula in Excel 365

=MOD(C3:C20 - C2:C19,1) * 1440

or more dynamic

=MOD(DROP(C.:.C,2) - DROP(DROP(C.:.C,1),-1),1) * 1440

To calculate the difference in minutes between two cells containing time stamps, you can use this formula:

=(C4 - C3) * 1440

• C4 - C3: calculates the difference between the current timestamp and the first timestamp.
• 1440: converts the difference from days to minutes (since there are 1440 minutes in a day).

Based on the last sentence, I guess you've found a formula which shows the difference not between the rows that are in the same column.

With the formula which you found you can move both minuend and subtrahend to the column "C" the second under the first. Thus, you'll get the required formula. Then you can copy this formula along column C to calculate other differences.

For example, you've found the formula to subtract A2 from A1. Put this formula into D3. Then move A2 to C2, A1 to C3. Then copy D3 and paste it to as many cells below as you need.

Since you did not mention any version constraints in your tags, this formula assumes 365.

A problem is that your dates are only present when the date changes. For easier math, you could copy down the date to fill in the blanks, so then you could subtract the datetimes easily and deal with the changing dates.

Fortunately, this can be done in the formula using the SCAN function.

NOTE: The only advantage of this over the much simpler formula from @TomSharpe is that it does not assume the dates are consecutive

Enter as formula in D2 only.
Results will SPILL down

=LET(
    d, B2:C29,
    dates, SCAN(INDEX(d, 1, 1), TAKE(d, , 1), LAMBDA(a, b, IF(b = "", a, b))),
    t, dates + TAKE(d, , -1),
    MAKEARRAY(
        ROWS(t),
        1,
        LAMBDA(r, c, IF(r = 1, 0, ROUND((INDEX(t, r) - INDEX(t, r - 1)) * 1440, 0)))
    )
)

• d is the original data range
• dates is the "filled in" date range
• t is the sum of the date+time columns => a datetime value
• and then we just do a simple subtraction, multiplying by 1440 and rounding to get the number of minutes.
• Be sure to format the last column as General

Enter =IF(C3>C2,(C3-C2)*1440,(1+C3-C2)*1440) in D2, and copy it down.

Explanation: The numeric value of time is a fraction of a day. For example, 3:29 PM is 0.645139. To convert a day into minutes, this number has to be multiplied by the number of minutes in a day (1440). Because the next time can be AM and hence smaller than the previous PM time, we have to add a day (1) to it. For example, the numeric value of 12:21 AM is 0.014583 and that of 11:27 PM is 0.977083. To get the correct difference, we have to add 1 to the smaller AM time. Hence, (1.014583 - 0.977083)*1440 = 54 minutes.
Note that the formula assumes that the difference in two consecutive times is less than 24 hours.
The formula with the MOD by Tom Sharpe also works, but I think the IF formula is easier to understand. The formula without MOD by Michal is clearly incorrect.