TB Spreadsheet

[clayweaver]

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 

[#2]

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
example file 

[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))

[#3]

@SamB513 

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.

[#4]

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".

[#5]

@SamB513 

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

[#6]

@clayweaver 

That was misprint in conditional formatting rule formula, please check attached.

[#7]

@Sergei Baklan 

whats the code i cannot find it on yours. even on excel its only showing my original code

[#8]

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()".