• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

C F Formula Help

L

Lauren Tatulli

New Member
Looking for formula to read:

IF cells in column E, starting with E3, are blank or 3 days greater than today (show in cell B1), red. If within 3 days of today, green.

Then i need to so the same for column F, starting F3 and column G, starting G3.

Thank you!
 
Last edited:
Hi Lauren, in the future always add a sample file please.
Perhaps it is like this...
upload_2018-4-11_20-0-44.png
see attached file for the formulas in CF. (note, it randomizes the data when you press F9).
 

Attachments

  • c-f-formula-help_38139.xlsx
    9.5 KB · Views: 2
Thank you for your help!

I'm not sure it's exactly what I need and I am somewhat confused by the data randomizing when pressing F9. I've attached the file.
 

Attachments

  • Joe_Job Tracking v4.xlsx
    10.3 KB · Views: 7
Hi ,

Having gone through your workbook , I would like to describe what I think should be the conditions for highlighting cells which fulfill the specified criteria ; please either clarify or confirm.

You have 3 dates in columns E , F , and G.

These dates will be filled in , in the order column E first , then column F , and then column G. Thus , if a particular row does not have a date in column E , it will not have any dates in column F or column G.

Similarly , if a particular row does not have a date in column F , it will not have any date in column G.

So now , let us assume a particular row has a date in column E ; suppose we consider cell E3. The date is 19 March 2018.

Now , if we consider that there is a particular time between a date being entered in column E , and a corresponding entry being made in column F , and if this time is 3 days , then as long as F3 is within 3 days of the date in E3 , we do not have a problem.

If however , the difference between F3 and E3 is more than 3 days , we need to highlight F3.

Suppose F3 is blank , then we look at the date in $B$1 , and check whether the difference between $B$1 and E3 is greater than 3 days ; if yes , we need to highlight the blank cell F3.

This same principle is extended to cells in column G.

If we consider that there is a particular time between a date being entered in column F , and a corresponding entry being made in column G , and if this time is 4 days , then as long as G3 is within 4 days of the date in F3 , we do not have a problem.

If however , the difference between G3 and F3 is more than 4 days , we need to highlight G3.

Suppose G3 is blank , then we look at the date in $B$1 , and check whether the difference between $B$1 and F3 is greater than 4 days ; if yes , we need to highlight the blank cell G3.

Do you agree with this logic ?

Narayan
 
Lauren Tatulli said:
Thank you for your help!

I'm not sure it's exactly what I need and I am somewhat confused by the data randomizing when pressing F9. I've attached the file.
Click to expand...
The randomizing was just to show random data. You need to look in the conditional formatting formulae.
But Narayan did ask some pertinent questions.
 
NARAYANK991 said:
Hi ,

Having gone through your workbook , I would like to describe what I think should be the conditions for highlighting cells which fulfill the specified criteria ; please either clarify or confirm.

You have 3 dates in columns E , F , and G.

These dates will be filled in , in the order column E first , then column F , and then column G. Thus , if a particular row does not have a date in column E , it will not have any dates in column F or column G.

Similarly , if a particular row does not have a date in column F , it will not have any date in column G.

So now , let us assume a particular row has a date in column E ; suppose we consider cell E3. The date is 19 March 2018.

Now , if we consider that there is a particular time between a date being entered in column E , and a corresponding entry being made in column F , and if this time is 3 days , then as long as F3 is within 3 days of the date in E3 , we do not have a problem.

If however , the difference between F3 and E3 is more than 3 days , we need to highlight F3.

Suppose F3 is blank , then we look at the date in $B$1 , and check whether the difference between $B$1 and E3 is greater than 3 days ; if yes , we need to highlight the blank cell F3.

This same principle is extended to cells in column G.

If we consider that there is a particular time between a date being entered in column F , and a corresponding entry being made in column G , and if this time is 4 days , then as long as G3 is within 4 days of the date in F3 , we do not have a problem.

If however , the difference between G3 and F3 is more than 4 days , we need to highlight G3.

Suppose G3 is blank , then we look at the date in $B$1 , and check whether the difference between $B$1 and F3 is greater than 4 days ; if yes , we need to highlight the blank cell G3.

Do you agree with this logic ?

Narayan
Click to expand...


Hi!! Thank you. I think that is looking a little more advanced than we need it to be.

Column E is Date the job came in. If that date in any cell in column E is blank OR 3 or more past "todays" date (in cell B1) than the cell or cells in column E need to be red. If less than 3 days, than the cell or cells should be green.

If date in any cell in column F (Date Parts Ordered) is blank OR 3 or more days past "todays" date (in cell B1) than the cell or cells in column E need to be red. If less than 3 days, than the cell or cells should be green.

If date in any cell in column G (Date Parts Received) is blank OR 3 or more days past "todays" date (in cell B1) than the cell or cells in column E need to be red. If less than 3 days, than the cell or cells should be green.
 
That is exactly it! Thank you!!

Will it continue if they add 100 rows? Also, how can I change it to always represent "today's" date?
 
Hi ,

The CF formulae are as follows :

RED : =AND($A3 <> "", OR(E3 = "", E3 > ($B$1 + 3)))

GREEN : =AND($A3 <> "", E3 <= ($B$1 + 3))

The Applies To range is :

=$E$3:$G$26

The rules themselves have nothing to do with the range , except that the top left cell in the Applies To range should be E3.

If you add data to your existing data , you will need to change the end point of the Applies To range from $G$26 to what ever will be the new end point after you have added data.

For example , if your data now extends to row 77 , your Applies To range will need to be changed to =$E$3:$G$77

Narayan
 
Why did you add A3 to the formula? The only thing I need for this, to be complete, is the dates entered in columns E, F and G, are all compared to B1 (Today's date). How would I add that to the current formulas?

Thank you
 
Hi ,

B1 is already being used.

You don't have to add anything to the formula.

A3 has been included so that if column A is blank , no formatting will be done.

Narayan
 
You must log in or register to reply here.
Back
Top