May 31 2022 05:52 PM
May 31 2022 05:52 PM
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
May 31 2022 11:19 PM - edited May 31 2022 11:22 PM
An IF() and EDATE() will get you points 1 & 3:
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
I guess ISBLANK() is probably better than ="", but effectively it's going to work the same for you:
Jun 01 2022 04:45 PM
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".
Jun 05 2022 08:53 PM
You have speech marks around "TODAY()" which is causing it to fail as Excel is treating it as text rather than a function, if you go to manage rules and edit the rule, you can delete the speech marks and it will work fine. It should only be TODAY() not "TODAY()".