Forum Discussion
TB Spreadsheet
An IF() and EDATE() will get you points 1 & 3:
=IF(A1="","",EDATE(A1,11))
IF A1 is blank, then blank, else take date from A1 and add 11 months.
Point 2 will be conditional formatting. simply have cell contents are < TODAY().
Attached is an example file, to view the conditional formatting go to "Home > Conditional Formatting > Manage Rules
https://tavernerresearch-my.sharepoint.com/:x:/g/personal/simonb_taverner_com_au/EXFJUsR4KxlJskDNacDd0VIBPASTdX5myqVxfW_S_4bLnA?e=AMLiq6
[edit]
I guess ISBLANK() is probably better than ="", but effectively it's going to work the same for you:
=IF(ISBLANK(A1),"",EDATE(A1,11))
points 1 and 3 worked. i guess i dont know how to conditianally format cells i tried it but it doesnt work. it still remains white.
For Cell Highlighting, highlight the range / column you want it applied to, click the "Conditional Formatting" option under the "Home" tab of the ribbon bar.
This will give you a drop down of options, you want the first one "Highlight Cell Rules", click on that you should get a sub menu where you're looking for the second option "Less Than..".
Selecting that should give you a pop up window, in the left hand box you will want to enter TODAY() and the default on the right of "Light Red Fill with Dark Red Text" is probably what you want, but you can adjust that further if you wish.
Clicking OK should apply it and will highlight any data less than today as red.
If you need to review the code applied, click Home > Conditional Formatting > Manage Rules - if nothing comes up try changing "This Selection" to "This Workbook".
This is what i keep getting the ones that are withing the timeframe are still red. if they are within the 11 month timeframe they should remain white but theyre still turning red
