I am trying to create a delivery driver log that automatically populates the discrepancy between expected return time and actual return time. So if a driver is expected to return at 2pm, but they return at 2:15pm, then I would like to see 15 in the discrepancy column. Inversely if the ETA is 2:15pm and they return at 2pm I want to see -15 as the discrepancy.
Google-sheets – Compute the difference between expected and actual time
google sheets
Related Solutions
By using SUMIFS (multiple criteria to define the sum range) instead of SUMIF (one criteria to define the sum range) and adding an extra column B which calculates the month number you can find that sum. The extra column is required because SUMIFS doesn't allow formulas like MONTH() as a criteria.
=SUMIFS(D1:D15,B1:B15,"8",C1:C15,"John Doe")
Use arrayformula to perform operations on arrays, for example
=sparkline(arrayformula(B2:E2 - A2:D2))
plots the sparkline of differences B2-A2,..., E2-D2.
Related Topic
- Google-sheets – Using multiple AND conditions in one IFS fromula
- Google-sheets – Google Sheets Pulling data between a specific date and time, from a timestamp
- Google Sheets – Calculate Difference Between Two Time Fields
- Google-sheets – Google Sheets – Formula to find difference between two values and recognize if the number difference is positive or negative
Best Answer
Subtraction, like
=A2 - B2, works: you just have to format the cell with this formula as Duration.By default, duration is displayed as 0:15:00 or -0:15:00 when negative. If seconds are not desired, select the cell and go to Format -> Number -> More formats -> More date and time formats and delete the fields you do not need from the format window.