Conditional Formatting

[Taryn A]

I have an Excel workbook with a lot of dates that are colored coded as follows

Red indicates that the date in the cell is 13 months past the date entered in the cell

Orange indicates that the date in the cell is 1 year past the date in the cell

Green indicates its within the year of the date entered in the cell.

As the workbook is set up now i have to manually change the color of the cell.

i know there is a formula using conditional formatting that can automatically change the date but I'm not sure what that formula is. I will need this for most of the workbook. Can anyone help?

 

[etaf]

you can post a spreadsheet using a share like dropbox or skydrive or any other file sharing website (some have adds and expect you to download other software - so I will not use them)

 

[Taryn A]

you can post a spreadsheet using a share like dropbox or skydrive or any other file sharing website (some have adds and expect you to download other software - so I will not use them)

I was able to post the spreadsheet right into the forum did you not see it?

 

[Taryn A]

I was able to post an image did you see that? Here it is again. Hopefully this will help since I can't seem to explain it very well lol! The thing is there is so many variables.

 

[etaf]

yes, i saw the screen shot - however, an actual spreadsheet can be more useful - and we can try setting up the rules, rather than recreate a sample from your picture
at least thats how i tend to work - and also use the add-in to display here

dropbox , skydrive are good places to share files

 

[Taryn A]

yes, i saw the screen shot - however, an actual spreadsheet can be more useful - and we can try setting up the rules, rather than recreate a sample from your picture
at least thats how i tend to work - and also use the add-in to display here

dropbox , skydrive are good places to share files

I have a dropbox account so what would you like for me to do?

 

[etaf]

share a link to the excel file

 

[Taryn A]

you can post a spreadsheet using a share like dropbox or skydrive or any other file sharing website (some have adds and expect you to download other software - so I will not use them)

Hey there, I put 2 spreadsheets in drop box in a folder called Conditional Formatting. One of the spreadsheets is updated so you can see the way it should be and the other one you can use to test the conditional formatting if you want. You already gave me the rules for the conditional formatting, but as I mentioned in my earlier posts there are so many variables, and you will see what I mean when you open the updated spreadsheet. If there are any blanks than the Cleared column would have a 1 under the cleared column "No" and should be coded red. This will hopefully make more sense when you see the actual spreadsheet. Thank you for being so patient with me on this! I appreciate your help!

https://www.dropbox.com/sh/azow8xziqx392rt/x7mj-e7kHB

 

[Taryn A]

I forgot to mention that the color code key is at the bottom of the spreadsheet. Thanks again!

 

[Taryn A]

Not sure if the link I previoulsy posted will work...maybe this one will.

Medical Physical Spreadsheet Updated.xlsx

Medical Physical Spreadsheet.xlsx

 

[etaf]

sorry to keep clarifying

I now have the spreadsheets and just need to understand all the rules for each colour

I see this in one of your posts
cell A6 = 3/26/10 - RED - 15 months
cell A7 = 4/24/12 - orange - 12 months
cell A8 = 4/17/13 - green - less than 12 months
But that does not align with the spreadsheet

Also you have three dates column E, J & O
How are we testing those ? which dates column are we using

RED, ORANGE & GREEN is mentioned in those rules above,

RED & ORANGE and appears to be required against any of the dates - so thats fine ,
BUT you also have BLUE and YELLOW

so I would like to have the rules for each of those colours and which Dates they need to apply to?