Formula to highlight times + or - 15 mins from a set time in a column

[Mark24931816]

<tbody>
[TD="class: votecell"]

[/TD]
[TD="class: answercell"]Hello Ladies and Gents


I have been tasked to do the below but have no idea where to start, very grateful for any help in solving this issue

I have populated the spreed sheet as below with the timings

• Column A = Time car should have arrived
• Column B = Time car actually arrived
• Column C = needs to highlight cars that arrived either early (Orange) or late (Red) by 15 min's or over either early or late from

Column A time

is this possible? And can anyone help me please

thank you in advance

[/TD]

</tbody>

 

[Gerald Higgins]

Hi, welcome to the board.

It's certainly possible, how do you want to highlight it exactly ?

Simplest would be a formula to write in the word "EARLY" or "LATE" or similar, but there are other ways, including conditional formating to apply colours.

 

[Mark24931816]

Hello Gerald

Many thanks for the swift response, basically i need to split a ton of time data into the ones that arrived either over or under 15 mins from the fixed time in column A, if i can display it in one column via a highlighted colour to differentiate between what was early and what was late.
or early in one column and late in another on the spread sheet so i can split them from the on time arrivals

i hope this makes some kind of sense

Mark

 

[Gerald Higgins]

OK, let's keep it simple to start with.

Let's assume you want to insert the word "EARLY" in one column, if the car arrived more than 15 minutes early.

Let's also assume that your times are proper Excel time values, not text strings.

=if(B2< (A2-(15/60/24)),"EARLY","")

This should insert EARLY into the cell if the car arrived more than 15 minutes early, and show a blank if it arrived less than 15 min early, or on time, or late.

Is this OK so far ?

 

[Mark24931816]

Hi Gerald

yes that is correct

 

[Gerald Higgins]

OK good, so if that's working, then you should be able to adapt that formula to do the lates as well.