Make cell background color if certain condition is fullfilled

[youneskil]

Hey guys!

In my table you see the two columns time and delta_time.
"time" indicates timestamps of an driverless transport system (DTS) while entering a certain workstation. "delta_time" indicates the time the DTS needed at the previous workstation.
In case of an problem the DTS will stop longer at a particual workstation and potentially causes a jam in the assembly line. To analyse which DTS was originally responsible for the jam I check
where the delta_time is higher then 00:01:30 (planned cycle time) and set this as local maximum.
You see in the table that the DTS stoped at workstation 02 for 00:02:48. After that delta_time decreases. That means that workstation 02 is the responsible for the jam.
The same you find at workstation 07.

Now I need an automated mechanism for painting green the the jam responsible workstations. I hope you got what I mean :D

Can you please help me?

ordernumber	date	time	delta_time	workstation
ZSC2	16.08.2021	07:03:00	00:00:00​	01
ZSC2	16.08.2021	07:05:48	00:02:48​	02
ZSC2	16.08.2021	07:07:29	00:01:41​	03
ZSC2	16.08.2021	07:09:07	00:01:38​	04
ZSC2	16.08.2021	07:10:34	00:01:27​	04
ZSC2	16.08.2021	07:11:59	00:01:25​	06
ZSC2	16.08.2021	07:14:38	00:02:39​	07
ZSC2	16.08.2021	07:16:46	00:02:08​	08
ZSC2	16.08.2021	07:18:14	00:01:28​	09
ZSC2	16.08.2021	07:19:46	00:01:32​	10

 

[etaf]

why isn't 1:41 , 1:38 highlighted - if over 1:30 ? 03 and 04
same for 08

highlighting over 1:30 would be simple with conditional formatting
BUT
how long before you count the next jam ? over 1:30

what if after the JAM - the next 10 workstations are all over 1:30

 

[youneskil]

Hi etaf, thanks for your response.
I do not highlight these because they are not responsible for the jam.
Rsponsible for the jam is only the one with the maximum delta_time. The following timestamps are also above 00:01:30 because they need time to solve the jam.
You see that after a maximum the delta_time decreases. When it starts increasing again that means there is another jam responsible DTS.

 

[etaf]

assuming the time ALWAYS decreases towards 1:30 after a block
then
=AND(D2>TIMEVALUE("0:1:30"), D2>D1)
could be used as conditional formatting

see formula entered in G , just to give an example of the results in spreadsheet - NOT needed for conditional formatting

if you want to highlight Columns D and E

then Select those 2 columns and use a rule
=AND($D2>TIMEVALUE("0:1:30"), $D2>$D1)

is 1min 30sec actually OK otherwise you need a > =

BUT will you have different order numbers in the same list ?

I have filled and changed font, you can JUST change font

Book4
	A	B	C	D	E	F	G
1	ordernumber	date	time	delta_time	workstation		Condition
2	ZSC2	16.08.2021	7:03:00	00:00:00	1		FALSE
3	ZSC2	16.08.2021	7:05:48	00:02:48	2		TRUE
4	ZSC2	16.08.2021	7:07:29	00:01:41	3		FALSE
5	ZSC2	16.08.2021	7:09:07	00:01:38	4		FALSE
6	ZSC2	16.08.2021	7:10:34	00:01:27	4		FALSE
7	ZSC2	16.08.2021	7:11:59	00:01:25	6		FALSE
8	ZSC2	16.08.2021	7:14:38	00:02:29	7		TRUE
9	ZSC2	16.08.2021	7:16:46	00:02:08	8		FALSE
10	ZSC2	16.08.2021	7:18:14	00:01:28	9		FALSE
11	ZSC2	16.08.2021	7:19:46	00:01:32	10		TRUE
Sheet1

Cell Formulas
Range	Formula
G2:G11	G2	=AND(D2>TIMEVALUE("0:1:30"), D2>D1)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:E19	Expression	=AND($D2>TIMEVALUE("0:1:30"), $D2>$D1)	text	NO

 

[youneskil]

Hey etaf, thanks for your help!
I forgot to tell you that content of column D is already calculated by a formular that is =IF(AND(A2=A1;C2>C1);C2-C1;0). Furthermore I have timeformat in column D.
Is it still possible to integrate your formular in mine?

 

[etaf]

its a conditional format formula
Did it NOT work ?

Its not entered into the cell itself, and so no need to integrate , i notice you are using ; instead of , , so replace my comma , with semicolon ;

Column G was just to show you the formula working, NOT needed at all
for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
D2:E1000 , change rows to suite your range - BUT note if you start at a different row number, the formula below has to be the same row number so if you select D10:E1000 - then the formula would start at row 10 =AND($D10>TIMEVALUE("0:1:30") ; $D10>$D9)

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=AND($D2>TIMEVALUE("0:1:30") ; $D2>$D1)

Format [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK