Forum Discussion
TB Spreadsheet
I am making a TB Spreadsheet for due immunizations. so it will have dates. and what id like help with is the dates will have blanks. this will be a living spreadsheet, so i need to be able to enter dates in lets say A1 and then the date in A2 would be 11months later, but if that A1 is blank then i would like A2 to remain blank. so i need help with a formula that will do 3 things
1. add about 11 month to the date last entered. (date immunization given)
2. cell to fill red if after the 11 month deadline.
3. have a blank cell if the date the immunizations was given is blank.
please help me i need it so very dearly
7 Replies
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".
