Make cell background color if certain condition is fullfilled

youneskil

New Member
Joined
Sep 22, 2021
Messages
11
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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?




ordernumberdatetimedelta_timeworkstation
ZSC216.08.202107:03:00
00:00:00​
01
ZSC216.08.202107:05:48
00:02:48
02
ZSC216.08.202107:07:29
00:01:41​
03
ZSC216.08.202107:09:07
00:01:38​
04
ZSC216.08.202107:10:34
00:01:27​
04
ZSC216.08.202107:11:59
00:01:25​
06
ZSC216.08.202107:14:38
00:02:39
07
ZSC216.08.202107:16:46
00:02:08​
08
ZSC216.08.202107:18:14
00:01:28​
09
ZSC216.08.202107:19:46
00:01:32
10
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
5,104
Office Version
  1. 365
Platform
  1. MacOS
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

New Member
Joined
Sep 22, 2021
Messages
11
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Oct 24, 2012
Messages
5,104
Office Version
  1. 365
Platform
  1. MacOS
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
ABCDEFG
1ordernumberdatetimedelta_timeworkstationCondition
2ZSC216.08.20217:03:0000:00:001FALSE
3ZSC216.08.20217:05:4800:02:482TRUE
4ZSC216.08.20217:07:2900:01:413FALSE
5ZSC216.08.20217:09:0700:01:384FALSE
6ZSC216.08.20217:10:3400:01:274FALSE
7ZSC216.08.20217:11:5900:01:256FALSE
8ZSC216.08.20217:14:3800:02:297TRUE
9ZSC216.08.20217:16:4600:02:088FALSE
10ZSC216.08.20217:18:1400:01:289FALSE
11ZSC216.08.20217:19:4600:01:3210TRUE
Sheet1
Cell Formulas
RangeFormula
G2:G11G2=AND(D2>TIMEVALUE("0:1:30"), D2>D1)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D2:E19Expression=AND($D2>TIMEVALUE("0:1:30"), $D2>$D1)textNO
 

youneskil

New Member
Joined
Sep 22, 2021
Messages
11
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Oct 24, 2012
Messages
5,104
Office Version
  1. 365
Platform
  1. MacOS
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
 

Forum statistics

Threads
1,147,735
Messages
5,742,869
Members
423,760
Latest member
photogfrog

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top