Time in and out

[welshspider]

Hi all I have a spreadsheet that I'm using that has a fixed start time column and a actual arrival time column with a column that has the difference,but only in minutes with no plus or minus,is there a formula that I can use to show the plus or minus in minutes with a possibility of a colour to show early or late
Many thanks
Mark
Start Time

Driver arrival time

Drivers mate arrival time

Driver Depart time

Difference

05:15



N/A



05:15

04:30



N/A



04:30

04:00







04:00

05:15



N/A



05:15

05:15



N/A



05:15

05:30



N/A



05:30

05:15



N/A



05:15

04:30



N/A



04:30







05:30



N/A



05:30

05:00



N/A



05:00

05:00



N/A



05:00

04:30



N/A



04:30

 

[shamimarita]

Hello Mark,

You can calculate the time difference between the fixed start time and the actual arrival time in minutes and also indicate whether the arrival was early or late using the following approach:

Step 1: Calculate the Time Difference
If your fixed start time is in A2 and the actual arrival time is in B2, use this formula in C2 to compute the difference in minutes:
=(B2 - A2) * 1440

• If the result is negative, it means the person arrived early.
• If the result is positive, it means the person was late.

Step 2: Display Early/Late Indications
To clearly show whether the person was early or late, you can use this formula in D2:

=IF((B2 - A2) * 1440 < 0, "Early", "Late")

Step 3: Apply Conditional Formatting
To visually highlight early and late arrivals:

• Select column C (or wherever your calculated difference is).
• Go to Home → Conditional Formatting → New Rule.
• Choose "Format only cells that contain".
• Set up two rules:
• For early arrivals: If the value is less than 0, format it in green.
• For late arrivals: If the value is greater than 0, format it in red.

This will allow you to track arrival times easily while making the data visually intuitive. Hope this helps! Let me know if you need any modifications.