Conditional formating using Funtion DAYS as a counter to change cell colour

M

Marcie Be

Board Regular
Joined
Jun 25, 2020
Messages
124
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
I have taken a screen shoot to explain what I am trying to achieve as I think it is the best way to communicate what I have done to date.
 

Attachments

  • Excel_Cell Formating Using DAYS to count.JPG
    Excel_Cell Formating Using DAYS to count.JPG
    113.4 KB · Views: 10

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
Are you looking for a cell formula, or a CF formula?
 
Upvote 0
I think I need both
Cell Formula to count the days
Conditional Format Formula to change the cell colour red amber green subject to the number of days elapsed.
I just want the cells to appear empty until dates are added to cells $AN4 to $AR4
 
Upvote 0
For the cell formula use
=if(ao6="","",days(ao6,an6))
 
Upvote 0
Thank you yes that works.
The issue is with the CF.
Because that formula produces a number -ve 4 digit number the cell turns red becuase my CF is >=5 format red
Only once a date is entered into AO6 does the -ve 4 didgit number in cell AO7 convert to a single digit to count the days between AO6 and AO7.
The single digit then activates the CF in the desired manner
1 to 2 days the cell is green
3 to 4 days the cell is amber
equal to greater than 5 days the cell is red
 
Upvote 0
I have tried the following cell formula
AN5=IF($AN4="","",IF($AO4="","",DAYS($AO4,$AN4)))
The intention here was that if $AN4 empty is then AN5 is empty, but if not and $AO4 is empty then $AN5 is still empty, otherwise it will count the days between the two dates in AN4 and AO4.
The above produces an empty cell that is red due to mt CF formating where >=5 is red
I have witten this CF in an attempt to achieve what I am looking for
$AN5=IF($AN4="","",IF($AO4="","",IF(DAYS($AO4,$AN4),IF($AN5>=5,"True","False")))) cell colour pink
I think this has fixed it. It works with CF as described below.
I still need to keep the CF >=5 Red for my traffic light colour coding for when the days counted are equal to or great than 5
Thank you for all your help
 
Upvote 0
Can you please post some sample data using the XL2BB add-in.
 
Upvote 0
OK will do
I am just applying the formula to the affected cells on one row AN to AR. I will post sample data to XL2BB
Thank you
 
Upvote 0
PLM Tracker Data Gathering Table V4.xlsm
ABCDEGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBHBIBJBKBL
1
2CROSS REFERENCE DASHBOARDDR for CR (DRR)CR DR for CN (DRN)CN DTRDR (DRR) TIMINGCR TIMINGDR (DRN) TIMINGCN TIMINGDTR TIMING
3ProgramChange TypeDescription of ChangeDTR Required Y/NTarget Completion DateActual Completion DateTotal numbers of days to CompleteLead EngineerRequesterSTATUS / COMMENTDR (DRR)CRDR (DRN)CNDTRDR NumberDR CREATION DATE# of Days DR OpenDR Next ActionCR NumberCR CREATION DATEFast Track Y/N# of Days CR OpenCR Next ActionDR NumberDR CREATION DATE# of Days DR OpenDR Next ActionCN NumberCN CREATION DATEFast Track Y/N# of Days CN OpenCN Next ActionTrufusion NumberDTR CREATION DATE# of Days CN OpenDTR Next ActionEngineerRequestorCAD LdrCAD DesgnrAuditor
4EXAMPLESW33PFast Track (CR)This is a Fast Track Change RequestY19/06/202019/07/202030MBWA N EngineerWith CAD Leader12344567N/A89101112123419/06/202011456726/06/2020Y4 891015/06/2020Y341112 29/06/202003/07/202007/07/202011/07/202015/07/2020
544444
6Cost OnlyN03/07/202013/07/202010MBWA N EngineerWork in ProgressN/A1314N/A1516N/A 1314  151629/06/2020 1429/06/202003/07/202005/07/202007/07/202012/07/2020
742251
8StandardYA N EngineerAwaiting Audit Approval17181920N/A212223241718 1920  2122 2324
9     
10Fast Track (CN)NN/AN/A27282930N/A   27282930 
11     
12Fast Track (CR)YN/AN/AN/AN/AN/A Y Y
13     
PLM2 Tracker (Sample Data)
Cell Formulas
RangeFormula
M4,M12,M10,M8,M6M4=IF($R4="","N/A",$R4)
N4,N12,N10,N8,N6N4=IF($V4="","N/A",$V4)
O4,O12,O10,O8,O6O4=IF($AA4="","N/A",$AA4)
P4,P12,P10,P8,P6P4=IF($AE4="","N/A",$AE4)
Q4,Q12,Q10,Q8,Q6Q4=IF($AJ4="","N/A",$AJ4)
X4,AG12,AG10,AG8,AG6,AG4,X12,X10,X8,X6X4=IF($C4="Fast Track (CR)","Y","")
Y4,Y12,Y10,Y8,Y6Y4=IF($W4="","",DATEDIF($W4,IF($H4>=0,TODAY()),"D"))
AC4AC4=IF($AB4="","",DATEDIF($AB4,IF($H4>0,H4,TODAY()),"D"))
AL4AL4=IF($AK4="","",DATEDIF($AK4,IF($H4>0,M4,TODAY()),"D"))
AN7:AQ7,AN5:AQ5AN5=IF(AN4="","",IF(AO4="","",DAYS(AO4,AN4)))
AR5,AR7AR5=IF(AR4="","",IF(H4="","",DAYS(H4,AR4)))
I4,I6I4=DAYS(H4,G4)
AH4,AH6AH4=IF($AF4="","",DATEDIF($AF4,IF($H4>0,H4,TODAY()),"D"))
AN9,AN13,AN11AN9=IF(AN8="","",DAYS(AN8,AN8))
AO13:AR13,AO11:AR11,AO9:AR9AO9=IF(AO8="","",DAYS(AO8,AN8))
T4,T12,T10,T8,T6T4=IF($S4="","",DATEDIF($S4,IF($H4>=0,TODAY()),"D"))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G6Cell Value>$AR6textNO
G6Cell Value<$AR6textNO
I4:I13Cell Valuebetween 1 and 9textNO
I6:I7Cell Valuebetween 10 and 19textNO
AN5:AR5,AN7:AR7Cell Value<=1textNO
AN7:AR7Cell Valuebetween 2 and 3textNO
AR7Expression=IF($AR6="","",IF($H6="","",IF(DAYS($H6,$AR6),IF($AR7>=4,"True","False"))))textNO
AQ7Expression=IF($AQ6="","",IF($AR6="","",IF(DAYS($AR6,$AQ6),IF($AQ7>=4,"True","False"))))textNO
AP7Expression=IF($AP6="","",IF($AQ6="","",IF(DAYS($AQ6,$AP6),IF($AP7>=4,"True","False"))))textNO
AO7Expression=IF($AN6="","",IF($AO6="","",IF(DAYS($AO6,$AN6),IF($AN7>=4,"True","False"))))textNO
I6:I7Cell Value>=20textNO
I4:I5Cell Valuebetween 10 and 19textNO
I4:I5Cell Value>=20textNO
H4:H123Cell Valuebetween 1 and 9textNO
H4:H123Expression=IF($I4>=10,AND($I4<=19,"False"))textNO
H4:H123Expression=$I4>20textNO
AN7Expression=IF($AN6="","",IF($AO6="","",IF(DAYS($AO6,$AN6),IF($AN7>=4,"True","False"))))textNO
AN5:AR5Cell Valuebetween 2 and 3textNO
AR5Expression=IF($AR4="","",IF($H4="","",IF(DAYS($H4,$AR4),IF($AR5>=4,"True","False"))))textNO
AQ5Expression=IF($AQ4="","",IF($AR4="","",IF(DAYS($AR4,$AQ4),IF($AQ5>=4,"True","False"))))textNO
AP5Expression=IF($AP4="","",IF($AQ4="","",IF(DAYS($AQ4,$AP4),IF($AP5>=4,"True","False"))))textNO
AO5Expression=IF($AO4="","",IF($AP4="","",IF(DAYS($AP4,$AO4),IF($AO5>=4,"True","False"))))textNO
AO13:AR13Expression=IF($AO$4="",)textNO
AO13:AR13Cell Valuebetween 2 and 3textNO
AO13Expression=IF($AO12="","",(DAYS($AO12,$AN12)))textNO
AO13:AR13Expression=IF($AO13="-","+","5")textNO
AN13:AR13Cell Value=1textNO
AN13Expression=$AN$6=""textNO
AN13:AO13Cell Value=""""""textNO
AO11:AR11Expression=IF($AO$4="",)textNO
AO11:AR11Cell Valuebetween 2 and 3textNO
AO11Expression=IF($AO10="","",(DAYS($AO10,$AN10)))textNO
AO11:AR11Expression=IF($AO11="-","+","5")textNO
AN11:AR11Cell Value=1textNO
AN11Expression=$AN$6=""textNO
AN11:AO11Cell Value=""""""textNO
AO9:AR9Expression=IF($AO$4="",)textNO
AO9:AR9Cell Valuebetween 2 and 3textNO
AO9Expression=IF($AO8="","",(DAYS($AO8,$AN8)))textNO
AO9:AR9Expression=IF($AO9="-","+","5")textNO
AN9:AR9Cell Value=1textNO
AN9Expression=$AN$6=""textNO
AN9:AO9Cell Value=""""""textNO
AN6:AR6Expression=$J$6=""textNO
AN5Expression=IF($AN4="","",IF($AO4="","",IF(DAYS($AO4,$AN4),IF($AN5>=4,"True","False"))))textNO
AJ4:AM123Expression=$E4="Y"textNO
AM3Cellcontains an errortextNO
AM3Cell Valuecontains "Y"textNO
AM3Cell Valuecontains "y"textNO
AM3Dates OccurringtodaytextNO
BC2:BD2Cellcontains an errortextNO
R4:U4,R14:U123,U6,U8,U10,U12,R5:T13Expression=$C4="Fast Track (CN)"textNO
Y4:Y13Expression=$C4="Fast Track (CN)"textNO
M4:Q4,M6:Q13,M5:N5,P5:Q5Cell Value="N/A"textNO
P4:P13Expression=$AF4=""textNO
Q4:Q13Expression=$AK4=""textNO
O4,O6:O13Expression=$AB4=""textNO
N4:N13Expression=$W4=""textNO
M4:M13Expression=$S4=""textNO
Q4:Q13Expression=$AL4>=2textNO
Q4:Q13Expression=OR($AL4=3,$AL4=4)textNO
Q4:Q13Expression=$AL$4>=5textNO
P4:P13Expression=$AH4<=2textNO
P3:P13Expression=OR($AH3=3,$AH4=4)textNO
P4:P13Expression=$AH$4>=5textNO
M4:M13Expression=$T4<=2textNO
O4,O6:O13Expression=$AC4<=2textNO
O4,O6:O13Expression=OR($AC4=3,$AC4=4)textNO
O4,O6:O13Expression=$AC4>=5textNO
M4:M13Expression=OR($T4=3,$T4=4)textNO
M4:M13Expression=$T4>=5textNO
N4:N13Expression=$Y4<=2textNO
N4:N13Expression=OR($Y4=3,$Y4=4)textNO
AL4:AL123Expression=$E$4="Y"textNO
AL4:AL13Expression=$AK$4=""textNO
AL4:AL123Expression=$C4="Fast Track (CR)"textNO
AL4:AL123Expression=$C4="Data Transfer"textNO
AL4:AL123Expression=$AK$4=""textNO
AL4:AL123Cell Valuebetween 1 and 2textNO
AL4:AL123Cell Valuebetween 3 and 4textNO
AL4:AL123Cell Value>=5textNO
AE4:AI123Expression=$C4="Fast Track (CR)"textNO
AA4:AA5Expression=$C4="Data Transfer"textNO
AA4:AD14,AA16:AD16,AA18:AD18,AA20:AD20,AA24:AD24,AA28:AD28,AA32:AD32,AA36:AD36,AA40:AD40,AA44:AD44,AA48:AD48,AA52:AD52,AA56:AD56,AA60:AD60,AA64:AD64,AA68:AD68,AA72:AD72,AA76:AD76,AA80:AD80,AA84:AD84,AA88:AD88,AA92:AD92,AA96:AD96,AA100:AD100,AA104:AD104Expression=$C4="Fast Track (CN)"textNO
Y4:Y13Expression=$C4="Data Transfer"textNO
Y4:Y13Expression=$W4=""textNO
Y4:Y13Cell Valuebetween 1 and 2textNO
Y4:Y13Cell Valuebetween 3 and 4textNO
Y4:Y13Cell Value>=5textNO
V4:Z13Expression=$C4="Fast Track (CN)"textNO
AH4:AH123Expression=$AF4=""textNO
AC4:AC5Expression=$AB4=""textNO
AH4,AH6Expression=OR($E4 = "DR", $E4 = "CN")textNO
AH4:AH123Cell Valuebetween 1 and 2textNO
AH4:AH123Cell Valuebetween 3 and 4textNO
AA4:AD14,AA16:AD16,AA18:AD18,AA20:AD20,AA24:AD24,AA28:AD28,AA32:AD32,AA36:AD36,AA40:AD40,AA44:AD44,AA48:AD48,AA52:AD52,AA56:AD56,AA60:AD60,AA64:AD64,AA68:AD68,AA72:AD72,AA76:AD76,AA80:AD80,AA84:AD84,AA88:AD88,AA92:AD92,AA96:AD96,AA100:AD100,AA104:AD104Expression=$C4="Cost Only"textNO
AA4:AD14,AA16:AD16,AA18:AD18,AA20:AD20,AA24:AD24,AA28:AD28,AA32:AD32,AA36:AD36,AA40:AD40,AA44:AD44,AA48:AD48,AA52:AD52,AA56:AD56,AA60:AD60,AA64:AD64,AA68:AD68,AA72:AD72,AA76:AD76,AA80:AD80,AA84:AD84,AA88:AD88,AA92:AD92,AA96:AD96,AA100:AD100,AA104:AD104Expression=$C4="Fast Track (CR)"textNO
AC4:AC14,AC16,AC18,AC20,AC24,AC28,AC32,AC36,AC40,AC44,AC48,AC52,AC56,AC60,AC64,AC68,AC72,AC76,AC80,AC84,AC88,AC92,AC96,AC100,AC104,AC108,AC112,AC116,AC120,AC124,AC128,AC22,AC26,AC30,AC34,AC38,AC42,AC46,AC50,AC54,AC58,AC62,AC66,AC70,AC74,AC78,AC82,AC86,AC90Cell Valuebetween 1 and 2textNO
R4:U4,R14:U123,U6,U8,U10,U12,R5:T13Expression=$C4="Cost Only"textNO
AC4:AC14,AC16,AC18,AC20,AC24,AC28,AC32,AC36,AC40,AC44,AC48,AC52,AC56,AC60,AC64,AC68,AC72,AC76,AC80,AC84,AC88,AC92,AC96,AC100,AC104,AC108,AC112,AC116,AC120,AC124,AC128,AC22,AC26,AC30,AC34,AC38,AC42,AC46,AC50,AC54,AC58,AC62,AC66,AC70,AC74,AC78,AC82,AC86,AC90Cell Valuebetween 3 and 4textNO
AB3Cell Valuecontains "Y"textNO
AB3Cell Valuecontains "y"textNO
AB3Dates OccurringtodaytextNO
N4:N13Expression=$Y4>=5textNO
T4:T13Expression=$S4=""textNO
T4:T123Cell Valuebetween 1 and 2textNO
T4:T123Cell Valuebetween 3 and 4textNO
M3:Q3Cellcontains an errortextNO
M3:Q3Cell Valuecontains "Y"textNO
M3:Q3Cell Valuecontains "y"textNO
M3:Q3Dates OccurringtodaytextNO
AK4:AK5Expression=($C4="Fast Track (CN)")textNO
AF4:AF5Expression=($C4="Fast Track (CN)")textNO
AC4:AC14,AC16,AC18,AC20,AC24,AC28,AC32,AC36,AC40,AC44,AC48,AC52,AC56,AC60,AC64,AC68,AC72,AC76,AC80,AC84,AC88,AC92,AC96,AC100,AC104,AC108,AC112,AC116,AC120,AC124,AC128,AC22,AC26,AC30,AC34,AC38,AC42,AC46,AC50,AC54,AC58,AC62,AC66,AC70,AC74,AC78,AC82,AC86,AC90Cell Value>=5textYES
AG3Cellcontains an errortextNO
AG3Dates OccurringtodaytextNO
G4Cell Value>$AR4textNO
G4Cell Value<$AR4textNO
T4:T123Cell Value>=5textNO
AH4:AH123Cell Value>=5textNO
AG4,AG6,AG8,AG10,AG12,AG14,AG16,AG18,AG20,AG22,AG24,AG26,AG28,AG30,AG32,AG34,AG36,AG38,AG40,AG42,AG44,AG46,AG48,AG50,AG52,AG54,AG56,AG58,AG60,AG62,AG64,AG66,AG68,AG70,AG72,AG74,AG76,AG78,AG80,AG82,AG84,AG86,AG88,AG90,AG92,AG94,AG96,AG98,AG100,AG102,AG104Expression=OR($E4 = "DR", $E4 = "CN")textNO
AK3Cellcontains an errortextNO
AK3Cell Valuecontains "Y"textNO
AK3Cell Valuecontains "y"textNO
AK3Dates OccurringtodaytextNO
S3Cell Valuecontains "Y"textNO
S3Cell Valuecontains "y"textNO
S3Dates OccurringtodaytextNO
AF3,AI3:AJ3Cellcontains an errortextNO
AF3,AI3:AJ3Cell Valuecontains "Y"textNO
AF3,AI3:AJ3Cell Valuecontains "y"textNO
AF3,AI3:AJ3Dates OccurringtodaytextNO
BP3:CF3,AN2:AO2,BM2:BN2,C3:L3,R3:X3,AS2:AT2,AX2:AY2,BH2:BI2,Z3:AE3Cellcontains an errortextNO
W3:X3,Z3:AE3Dates OccurringtodaytextNO
 
Upvote 0
From what I can see that is working, so what's the problem?
 
Upvote 0
You must log in or register to reply here.

Similar threads

Y
  • Question
Conditional formatting
Replies
3
Views
158
SanjayGMusafir
SanjayGMusafir
A
  • Question
VBA to change cell colour based on content
Replies
3
Views
266
Snakehips
Snakehips
O
  • Solved
mod(row() change font colour as required
Replies
7
Views
276
Peter_SSs
Peter_SSs
J
  • Question
Custom formatting text colour
Replies
3
Views
112
Fluff
Fluff
A
  • Solved
Within cell progress bar for Working days?
Replies
5
Views
339
felixstraube
F

Forum statistics

Threads
1,215,779
Messages
6,126,850
Members
449,344
Latest member
TFXm

Share this page

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
Back
Top