IF Function

[JoeG2124]

I am not a beginner, but certainly not an expert so maybe someone can help me.

Right now, I have the below Formula in column S and it is working like it is supposed to. In column S, it will populate the word "Red" "Amber" or "Green" based on the difference between the 2 dates in column H&I. Then using conditional formatting that will light up the cell (H2) Red, Amber or Green (S2 = "Green" then H2 lights up Green and so on). I have it now so that if nothing is in column I, then column S stays blank (the "" at the end of the function).

IF(I2|IF(H2|
IF(I2-H2<=13| "Red"|
IF(I2-H2<=27| "Amber"|
"Green"))|
IF(TODAY()-I2<=-28| "Green"|
IF(TODAY()-I2<=-14| "Amber"|
"Red")))|"")

Now I need to include a line for if someone puts the word "Remote" in column H, it needs to return "Green" in column S. The way I have it now CAN work, but that would mean having 2 different formulas and I want to make it so all the cells in Column S all have the same formula. With that first line added, I had to take away the "" that made column S stay blank if nothing was in column I. But I need column S to stay blank when nothing is in column I. After unsuccessfully trying to figure it out for a while, I came here.

IF(H2="Remote"|"Green"|
IF(I2|IF(H2|
IF(I2-H2<=13|"Red"|
IF(I2-H2<=27|"Amber"|
"Green")))|
IF(TODAY()-I2<=-28|"Green"|
IF(TODAY()-I2<=-14|"Amber"|
"Red"))))

 

[GR00007]

Interesting that you have your Region settings to have Pipe (|) instead of comma.

Will this work?

=IF(I2|IF(H2="Remote"|"Green"|IF(H2|IF(I2-H2<=13| "Red"|IF(I2-H2<=27| "Amber"|"Green"))|IF(TODAY()-I2<=-28| "Green"|IF(TODAY()-I2<=-14| "Amber"|"Red"))))|"")

 

[JoeG2124]

Thank You @GR00007... that work perfectly. Sorry about the delay in responding, I just got back to this.

I use the pipe in my formulas because that is how the person at my work did it and they were the one that "trained" me. They were better at Excel than me, so I figured I would just copy them. They have abandoned me and left me with spreadsheets that I need to keep updated.

 

[GR00007]

I've found many a help on this site.
If you want to stop using pipe, you can check this out:
https://www.mrexcel.com/excel-tips/commas-stop-working-in-formulas/