LinkBack URL
About LinkBacks
Hello,
I am trying to set a conditional format (cell color) for column N if the date is greater than 7 days from column K. Any suggestions? I am using Excel 2010.
Hello,
I am trying to set a conditional format (cell color) for column N if the date is greater than 7 days from column K. Any suggestions? I am using Excel 2010.
Try this formula in CF
=$N2>$K+7
Thank you Jose. I received an error message stating there is a problem with the formula.
don't copy but type by hand in CF
=$N2>$K2+7
Last edited by sandy666; 01-17-2017 at 07:43 PM.
Thank you Sandy. This worked but some of highlights are not valid. For example,I have the date of 11/7/2016 in column K and N but it the formula above highlighted it. It did not highlight all the cells in the N column with the same date as K though. It also missed some cells that are more than 7 days. I went to conditional formatting, manage rule, new rule, use formula to determine which cells to format. Am I doing something wrong? Any suggestions?
Read Conditional Formatting or here Change, find, or clear conditional formats dialog box options or here Excel's conditional formatting feature
Did you read Excel Help about Conditional Formatting?
Last edited by sandy666; 01-18-2017 at 04:11 PM. Reason: links added
Than you so very much for your help Sandy. It looks like I am doing everything correctly. But it is still not highlighting all the appropriate cells.
Are your dates real dates, or text looking like dates?
Test with =isnumber(cell-ref) FALSE indicates text, and we will need to convert them
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Thank you Ford. They are all dates. Excluding a few cells that are blank in the N column.
can you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.
It looks like this?
Definitely. I had to remove confidential information but I kept the two columns I am working with.
Sorry. I forgot to attach the example.
Hm, what is wrong with dates in your file?
Last edited by sandy666; 01-18-2017 at 05:27 PM.
I manually colored the cells to show Ford what I want it to look like. This sheet does not have the conditional formatting.
you should attach sample with errors and what you want to achieve. look at my attachment in #11 with CF
btw. you have ex2010 so why example is xls not xlsx ?
Based on your sample, this should work...
=$B2>$A2+7
Maybe it need to work on <=2003?Originally Posted by sandy666
Look at attachment with your dates without CF and your dates with CF
If it is not what you want I give up
I'm sorry. Attached is the file I am working with. The other file was incorrect.
I'm sorry. They recently upgraded my excel to 2016.
In your attached file, you have the wrong references. Your CF says...
=$B2>$A2+7
it should say...
=$B3>$A3+7
In your latest file none of the dates are more than 7 days apart, so any CF based on that condition will not trigger - attach a more representative set of data.
Pete
your data of dates start in A3 / B3, your CF start in A2 / B2, Change formula in CF to A3 and B3 and check again
edit: ups, I am late
row 41 and 48 is, Pete
C3=$B3>$A3+7
copied down
Ah yes - it's difficult scanning manually, especially as the dates are back to front to me.
Pete
Thank you everyone so very much! =$B3>$A3+7 worked!!!
I feel you. I am a Brit, grew up in South Africa, and now live in USA. Darn mercans do everything farse about ace lol
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks