SOLVED

Trouble with Conditional formatting

%3CLINGO-SUB%20id%3D%22lingo-sub-2581498%22%20slang%3D%22en-US%22%3ETrouble%20with%20Conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2581498%22%20slang%3D%22en-US%22%3E%3CP%3ELet%20me%20try%20and%20explain%20what%20I%20am%20trying%20to%20do%20here.%20I%20have%20a%20series%20of%20columns%20with%20the%20date4%20for%20the%20Monday%20of%20every%20week.%20This%20goes%20out%20for%202%20years.%20I%20am%20listing%20electronic%20components%20that%20have%20different%20lead-times.%20I%20want%20the%20color%20of%20the%20dates%20to%20change%20when%20the%20date%20exceeds%20today's%20date%20plus%20the%20lead-time%20number.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20use%20the%20week%20number%20plus%20the%20lead-time%20number%20to%20change%20the%20color%20of%20a%20cell%3C%2FP%3E%3CP%3EI%20can%20enter%20this%20formula%20into%20a%20cell%20and%20it%20resolves%20correctly%3A%3C%2FP%3E%3CP%3E%3DIF(WEEKNUM(E8)%26gt%3B(WEEKNUM(%24A%241)%2B%24B9)%2C%22%22%2C%22OK%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%20if%20I%20enter%20this%20same%20formula%20into%20the%20conditional%20formatting%20it%20does%20nothing.%20It%20does%20not%20resolve%20at%20all.%20Of%20course%20under%20the%20formula%26nbsp%3Bconditional%20formatting%20is%3C%2FP%3E%3CP%3E%22IF(WEEKNUM(E8)%26gt%3B(WEEKNUM(%24A%241)%2B%24B9)%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAm%20I%20missing%20something%20here%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3EPat%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2581498%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2581649%22%20slang%3D%22en-US%22%3ERe%3A%20Trouble%20with%20Conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2581649%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1111343%22%20target%3D%22_blank%22%3E%40Pat_Forster%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EUse%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DWEEKNUM(E8)%26gt%3BWEEKNUM(%24A%241)%2B%24B9%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2592231%22%20slang%3D%22en-US%22%3ERe%3A%20Trouble%20with%20Conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2592231%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3BThank%20you%20for%20your%20input.%20I%20was%20able%20to%20get%20this%20operating%20the%20way%20that%20I%20want.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20help.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Let me try and explain what I am trying to do here. I have a series of columns with the date4 for the Monday of every week. This goes out for 2 years. I am listing electronic components that have different lead-times. I want the color of the dates to change when the date exceeds today's date plus the lead-time number.

 

I am trying to use the week number plus the lead-time number to change the color of a cell

I can enter this formula into a cell and it resolves correctly:

=IF(WEEKNUM(E8)>(WEEKNUM($A$1)+$B9),"","OK")

 

However if I enter this same formula into the conditional formatting it does nothing. It does not resolve at all. Of course under the formula conditional formatting is

"IF(WEEKNUM(E8)>(WEEKNUM($A$1)+$B9)"

 

Am I missing something here?

 

Thanks,

Pat 

2 Replies
best response confirmed by allyreckerman (Microsoft)
Solution

@Pat_Forster 

Use

 

=WEEKNUM(E8)>WEEKNUM($A$1)+$B9

 

 

@Hans Vogelaar Thank you for your input. I was able to get this operating the way that I want.

 

Thanks for your help.