“Days” is the name of a worksheet in the V Planner and it is the official school calendar for the planner. There is another worksheet in the V Planner called Calendar, but that is more of a personal calendar and it has nothing to do with school dates. This is about the High School V Planner and the Days worksheet. What is different about Days in the new planner?
One thing is different – All four school year calendars are on Days and can be set up. Keep this in mind, the V Planner is a spreadsheet file and changes can be made to the calendars at any time during the year.
Days in the High School V Planner
This is a screenshot of the upper section of the four calendars. In the high school version of the V Planner I have retained the colors that highlight certain things – every color has a purpose and that is explained on the right side of the sheet (not shown in the image).
Click on image to see full size
Most of the colors used in this sheet rely on conditional formatting. What that means is that if a condition changes, then the formatting can change, which, in the case of the Days sheet highlights something. In case you are curious, conditional formatting triggers on things which are either true or false. That means that the formula that is used must result in either true or false.
Green Y and ISTEXT
For instance:
It is important that the “Y’s” that mark attendance mark official school days. If attendance is marked for a school day, the Y will be green, if attendance is marked on a non-school day, the Y will be red. This is important because red Y’s do not count on the report cards. To make this happen I used a conditional formatting in the cells where the “Y’s” are typed.
When you type a Y to mark attendance, a formula will check the column that has the “X’s”. If there is an “X” (true) then the Y will be green. The formula for that is
=ISTEXT( cell ref )
This basically asks Does this cell have text?
Once a formula is added, I was able to add formatting. In the case here – a gray background with green text and bold letters.
Red Y and ISBLANK
When a Y is typed another formula checks to see if the “X” cell is blank (true). If it is blank the “Y” will be red.
The formula for that is
=ISBLANK( cell ref )
This basically asks Is this cell blank?
This question results in a yes/no answer which, to Excel, is True/False and conditional formatting requires true/false answers.
The other effect of the (=ISBLANK( cell ref )) conditional formatting is the dark background that is created on the non-school days in the attendance column. With or without a Y, the background is darkened which should alert you to non-school days when you are marking attendance. As mentioned earlier, this planner is a spreadsheet and if attendance needs to be counted for a certain non-school day, then mark it as a school day.
One other thing, the weekends are highlighted with a yellow background. That is also conditional formatting and the formula for that is
For Saturday: =WEEKDAY( cell ref )=7
For Sunday: =WEEKDAY( cell ref )=1
Microsoft reference link for WEEKDAY
Microsoft reference link for IS
At DonnaYoung.org – The current help page for the Days worksheet for V Planner V2.5
