Yearly Planner

Hi,

Similar to a previuos post (by sgt.trojan) is there anyway I could do similar with the attached planner??

The layout is opposite months are horisontal and days vertical!!

Thanks

Col

Originally Posted by 6StringJazzer

Here is the transposition. I have assumed that you will always keep this format rather than rolling months off to the left. That is, it will always show Aug-Dec of one year then Jan-July of the next.

You need to rid of the merged cells for the weekdays. It's going to be different every year and the whole point of all this is automation; merging is manual. You may have to tweak your conditional formatting a bit.

Per your PM:
I'm sure this is possible. Um, what's a CF?

Conditional Fornatting, being a bit lazy in not typing the the full meaning LOL.

Can you give me a version where the cells are not merged along with the conditional formatting rules, your formulas are amazing and way above what I am capable of, it will take me eons to work out what you have done but a great opportunity for me to learn LOL

I'll try to get back to the conditional formatting (CF--I should have known that).

Here is the breakdown of logic used for the first week of the month. Special logic is only needed for the first week, because the first day of the month has to fall in one of the first 7 rows. After that you can simply add 1 to get the next date.

=IF(

WEEKDAY(B$2,2+N("Comment: 2 means that Monday=1")) This expression returns a number that corresponds to what day of the week it is for B$2, which is the first day of that month. The 2 says, "Return 1 for Monday, and so on, until 7 for Sunday." The function N returns a zero if you put text inside, so I sometimes use that to put a comment in a formula. You could just simply use

WEEKDAY(B$2,2)

=ROW()-3, I need to calculate the day number for the day on the current row. The first day is Monday in row 4, so if I subtract 3 it will give me a day number that I can compare to the result of the WEEKDAY function. Recall that WEEKDAY will return 1 if the day is a Monday.

B$2, If that comparison is true, that is, if the first day of the month falls on the day of the week for this row, then the date used for this day will be the first day of the month, which is in B$2.

IF( But if it's not true, then we have to check...

B4="","", to see if the day before is blank. If this row is the not the first day of the month, and the row before is blank then we haven't reached the first day yet so this date will be blank too.

B4+1) But if the previous day is nonblank, then today must be one day later, so we add 1 to it to get the date for this row.

)

)

Similar logic has to be used at the end of the month as well. If the calculation for that row would go past the end of the month, then the date is just left blank.