Spreadsheet Practice

How to calculate how many minutes late someone is in Excel

Formula (Excel and Google Sheets)

=MAX(0,(B2-$F$1)*1440)

Subtract the start time, convert to minutes and floor at zero: =MAX(0,(B2-$F$1)*1440).

Result on the example
one result per row in C2, filled down to C7
Works in
Every current version
Google Sheets
Same formula

See it work

The formula is already in C2, filled down to C7. Change any input and the result updates, or edit the formula to experiment.

How it works

B2-$F$1 is how far past the start the arrival was, as a fraction of a day, and multiplying by 1,440 turns it into minutes. MAX(0, …) turns early arrivals into 0 instead of negative minutes: Dev arrived at 8:31, 31 minutes late.

=MAX(0,(B2-$F$1)*1440)

Result0

  1. B27:56 AM

    The value in B2.

  2. $F$18:00 AM

    The value in F1.

  3. B2-$F$1-0.002777777778

    The - operator subtracts.

  4. B2-$F$1)*1440-4

    The * operator multiplies.

  5. MAX(0,(B2-$F$1)*1440)0

    MAX: Largest number.

    • number10
    • number2B2-$F$1)*1440-4

In Google Sheets

The same formula works in Google Sheets: =MAX(0,(B2-$F$1)*1440). We ran it in Google Sheets mode on the same data and it returns the same results.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=MAX(0,ROUND((B2-$F$1)*1440,0))0, 0, 7…Different resultWhole minutes. ROUND also removes floating-point noise, so 7 never shows as 6.9999999. Rounds to whole minutes when the times include seconds.
=IF(B2>$F$1,"Late","On time")On time, On time, Late…Different resultJust a flag. Late or On time.

Common mistakes

  • Without MAX, early arrivals show negative minutes.
  • Time arithmetic can give 6.9999999 instead of 7. Round it before comparing, for example with a grace period: =IF(ROUND((B2-$F$1)*1440,0)>5,ROUND((B2-$F$1)*1440,0),0) ignores up to 5 minutes.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Calculate how many minutes late someone is

In C2, calculate how many minutes after the shift start in F1 each person arrived, showing 0 for anyone on time. Fill down to C7.

Questions

How do I show lateness as h:mm?

=MAX(0,B2-$F$1), formatted as h:mm.

How do I count late arrivals?

=COUNTIF(C2:C7,">0").

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.