SOLVED

HELP required with formula or conditional formatting....

%3CLINGO-SUB%20id%3D%22lingo-sub-2267914%22%20slang%3D%22en-US%22%3EHELP%20required%20with%20formula%20or%20conditional%20formatting....%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2267914%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%20hoping%20that%20i%20can%20get%20some%20good%20advice%20and%20hopefully%20an%20answer%20to%20my%20problem.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20spreadsheet%2C%20picture%20attached%2C%20I%20already%20have%20a%20few%20formula%20and%20conditional%20formatting%20rules%20applied.%3C%2FP%3E%3CP%3EI%20would%20like%20to%20change%20'column%20I'%20to%20one%20of%203%20values%2C%20based%20on%20the%20date%20values%20in%20columns%20D%20and%20E.%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20both%20dates%20have%20passed%2C%20then%20D%26amp%3BE%20auto%20change%20to%20green%2C%20Todays%20date%20stays%20white%2C%20dates%20in%20the%20future%20are%20yellow%2C%20and%20TBC%20(to%20be%20confirmed)%20are%20red.%3C%2FP%3E%3CP%3EIn%20Column%20I%2C%20these%20are%20reflected%20by%20%22complete%22%2C%20%22Pending%22%20and%20%22incomplete%22.%20I%20have%20to%20manually%20change%20these%20at%20the%20moment%20(using%20the%20data%20validation%20list).%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20question%20is%2C%20is%20there%20a%20way%20to%20change%20the%20status%20in%20column%20I%20automatically%2C%20based%20on%20the%20values%20in%20columns%20D%20and%20E.%3C%2FP%3E%3CP%3ESo%20far%20I%20have%20avoided%20any%20macros%2C%20which%20am%20hoping%20to%20avoid%20with%20this%20also.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20very%20much%20appreciated.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22SP8Y8_1-1618235346780.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F271981i83B624108E530746%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22SP8Y8_1-1618235346780.png%22%20alt%3D%22SP8Y8_1-1618235346780.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2267914%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2268020%22%20slang%3D%22en-US%22%3ERe%3A%20HELP%20required%20with%20formula%20or%20conditional%20formatting....%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2268020%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1023648%22%20target%3D%22_blank%22%3E%40SP8Y8%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20haven't%20specified%20under%20which%20conditions%20the%20status%20should%20be%20N%2FA%20or%20Cancelled.%20Here%20is%20a%20first%20attempt.%20It%20will%20probably%20have%20to%20be%20modified%2Fexpanded.%20In%20I7%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIF(OR(D7%3D%22TBC%22%2CE7%3D%22TBC%22)%2C%22Incomplete%22%2CIF(OR(D7%26gt%3BTODAY()%2CE7%26gt%3BTODAY())%2C%22Pending%22%2C%22Complete%22))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFill%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2268118%22%20slang%3D%22en-US%22%3ERe%3A%20HELP%20required%20with%20formula%20or%20conditional%20formatting....%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2268118%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%3BHi%20Hans%2C%3CBR%20%2F%3EI%20tried%20that%20formula%20and%20unfortunately%20the%20value%20changed%20in%20I7%20to%20%22Complete%22%20every%20time%2C%20despite%20the%20value%20entered%20in%20D%20and%20E.%3CBR%20%2F%3EIn%20the%20I%20column%2C%20there%20has%20to%20be%20a%20rule%20order%2C%3C%2FP%3E%3CP%3E1.%20if%20either%20cell%20in%20the%20D%20and%20E%20columns%20has%20a%20%22TBC%22%20then%20the%20I%20cell%20should%20be%20%22incomplete%22%2C%3CBR%20%2F%3E2.%20if%20either%20of%20the%20D%20and%20E%20columns%20has%20a%20date%20in%20the%20future%20(yellow)%20then%20the%20I%20cell%20should%20read%20%22Pending%22%3CBR%20%2F%3E3.%20Only%20if%20both%20D%20and%20E%20are%20dates%20that%20have%20passed%20or%20todays%20date%20then%20I%20cell%20should%20read%20%22Complete%22%3C%2FP%3E%3CP%3EI%20have%20attached%20a%20copy%20of%20the%20work%20sheet%20to%20see%20if%20that%20is%20of%20any%20help%20to%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi all, hoping that i can get some good advice and hopefully an answer to my problem.

 

I have a spreadsheet, picture attached, I already have a few formula and conditional formatting rules applied.

I would like to change 'column I' to one of 3 values, based on the date values in columns D and E. 

If both dates have passed, then D&E auto change to green, Todays date stays white, dates in the future are yellow, and TBC (to be confirmed) are red.

In Column I, these are reflected by "complete", "Pending" and "incomplete". I have to manually change these at the moment (using the data validation list). 

My question is, is there a way to change the status in column I automatically, based on the values in columns D and E.

So far I have avoided any macros, which am hoping to avoid with this also.

 

Any help would be very much appreciated.

SP8Y8_1-1618235346780.png

 

 

 

9 Replies

@SP8Y8 

You haven't specified under which conditions the status should be N/A or Cancelled. Here is a first attempt. It will probably have to be modified/expanded. In I7:

 

=IF(OR(D7="TBC",E7="TBC"),"Incomplete",IF(OR(D7>TODAY(),E7>TODAY()),"Pending","Complete"))

 

Fill down.

@Hans Vogelaar Hi Hans,
I tried that formula and unfortunately the value changed in I7 to "Complete" every time, despite the value entered in D and E.
In the I column, there has to be a rule order,

1. if either cell in the D and E columns has a "TBC" then the I cell should be "incomplete",
2. if either of the D and E columns has a date in the future (yellow) then the I cell should read "Pending"
3. Only if both D and E are dates that have passed or todays date then I cell should read "Complete"

I have attached a copy of the work sheet to see if that is of any help to you.

 

Regards

best response confirmed by SP8Y8 (Occasional Contributor)
Solution

@SP8Y8 

In your screenshot, the data began in row 7. In your workbook, they begin in row 4, so the formula has to be adjusted accordingly. In the following formula for I4, I also took empty cells into account:

 

=IF(AND(D4="",E4=""),"",IF(OR(D4="TBC",E4="TBC"),"Incomplete",IF(OR(D4>TODAY(),E4>TODAY()),"Pending","Complete")))

apologies, for the confusion there, I have now applied this and it works! Thanks you so much for your help Hans.

@Hans Vogelaar Slight issue that I did not foresee, I will need the following added to the formula,

If D or E column have PAO or N/A in it, then column I should read complete or incomplete based on the date in the other column..... As per attachment.......Help again please?

@SP8Y8 

Here is a new version:

 

=IF(AND(D4="",E4=""),"",IF(OR(D4="TBC",E4="TBC"),"INCOMPLETE",IF(OR(D4={"N/A","PAO"},E4={"N/A","PAO"}),IF(MAX(D4:E4)>TODAY(),"COMPLETE","INCOMPLETE"),IF(MAX(D4:E4)>TODAY(),"PENDING","COMPLETE"))))

@Hans Vogelaar 

Good morning, I have encountered a problem with the latest version you very kindly constructed for me.

PAO and N/A gave incorrect values in the status column.

I have updated the sheet and added a few comments in the hope this may be of benefit if you can still assist me.

 

Kind regards.

 

 

@SP8Y8 

Sorry, I switched a condition.

 

=IF(AND(D4="",E4=""),"",IF(OR(D4="TBC",E4="TBC"),"INCOMPLETE",IF(OR(E4={"N/A","PAO"}),IF(D4>TODAY(),"INCOMPLETE","COMPLETE"),IF(MAX(D4:E4)>TODAY(),"PENDING","COMPLETE"))))

 

If I8 and I10 should be pending, contradicting your earlier description:

 

=IF(AND(D4="",E4=""),"",IF(OR(D4="TBC",E4="TBC"),"INCOMPLETE",IF(OR(E4={"N/A","PAO"}),IF(D4>TODAY(),"PENDING","COMPLETE"),IF(MAX(D4:E4)>TODAY(),"PENDING","COMPLETE"))))

@Hans Vogelaar absolutely brilliant!  This works perfectly. Thanks you very much for all your time and help. Very much appreciated.

 

Colin.