Conditional Format Calendar

Hi,

I'm using Excel 2011 for mac and have opened a multi page calendar template. I would like to conditional format different days in 2 different colours.

What I would like to achieve is a conditional format that will highlight the first 5 days in colour 1, the next 5 days in colour 2, the following 2 days in colour 1 and the final 2 days in colour 2 and repeat this throughout the year. This would help me create a joint custody 5-5-2-2 schedule without having to pay for software.

To add to the above I would also require some colour scaling in there also. The colour scale would only need to be at the beginning/end of a 5 block or 2 block for both colours.

Essentially, what I'd like to achieve is the same as the image below.

2-2-5-5-calendar.png

Your help will be greatly appreciated.

I look forward to reading responses.

Many users are unable to view embedded .png files. Are you able to post a workbook using Go Advanced --> Manage Attachments?

Thanks for the response. I've attached it, I've added the image into the last worksheet and labelled it 'Desired Result'.

I look forward to seeing if you can help.

I'm about to call it a day, but let me get you started. Select B3:H8 on your January sheet and create a new conditional formatting rule using the formula below:

=OR(MOD(B3-DATE(YEAR(B3),1,0)-1,14)<5,MOD(B3-DATE(YEAR(B3),1,0)-1,14)=10,MOD(B3-DATE(YEAR(B3),1,0)-1,14)=11)

Pick the color of your choice. Repeat the process on the other months, using the exact same range and (unedited) formula, and you'll have the year successfully split into your 5/5/2/2 division. The formula is based on day of the year, so it should carry over cleanly between months. You don't need two CF formulas for your 2 colors, just color every day one color, and use the CF formula to color over the formatted half of them in the second color. I've never really played with color scaling before - maybe someone can jump in and help you there - but hopefully this gets you on your way before I step out for the day. If you have questions, I should be able to check in at some point tomorrow. Good luck!

Originally Posted by cantosh

I'm about to call it a day, but let me get you started. Select B3:H8 on your January sheet and create a new conditional formatting rule using the formula below:

=OR(MOD(B3-DATE(YEAR(B3),1,0)-1,14)<5,MOD(B3-DATE(YEAR(B3),1,0)-1,14)=10,MOD(B3-DATE(YEAR(B3),1,0)-1,14)=11)

Pick the color of your choice. Repeat the process on the other months, using the exact same range and (unedited) formula, and you'll have the year successfully split into your 5/5/2/2 division. The formula is based on day of the year, so it should carry over cleanly between months. You don't need two CF formulas for your 2 colors, just color every day one color, and use the CF formula to color over the formatted half of them in the second color. I've never really played with color scaling before - maybe someone can jump in and help you there - but hopefully this gets you on your way before I step out for the day. If you have questions, I should be able to check in at some point tomorrow. Good luck!

Perfect, that did the trick. Now for just colour scaling (with a gradient) but I fear it may not be achievable with your formula due to colour scale CF can't use relative references. I may just stick with what I've got, it should work for what I need.

Kudos

Originally Posted by cantosh

I'm about to call it a day, but let me get you started. Select B3:H8 on your January sheet and create a new conditional formatting rule using the formula below:

=OR(MOD(B3-DATE(YEAR(B3),1,0)-1,14)<5,MOD(B3-DATE(YEAR(B3),1,0)-1,14)=10,MOD(B3-DATE(YEAR(B3),1,0)-1,14)=11)

Pick the color of your choice. Repeat the process on the other months, using the exact same range and (unedited) formula, and you'll have the year successfully split into your 5/5/2/2 division. The formula is based on day of the year, so it should carry over cleanly between months. You don't need two CF formulas for your 2 colors, just color every day one color, and use the CF formula to color over the formatted half of them in the second color. I've never really played with color scaling before - maybe someone can jump in and help you there - but hopefully this gets you on your way before I step out for the day. If you have questions, I should be able to check in at some point tomorrow. Good luck!

I do have 1 question for you for tomorrow now that I'm playing with it... when I change the year, it doesn't follow on, it just starts again. Any way to have it follow on? Perhaps something to add to the formula to prompt it to start 3/4/5 days later that I can easily edit year on year?

I'm afraid I don't know of a color scaling solution, but in response to your second question: the formula is rooted into the date in cell B3, which is tied to the selected year, so the formula as it stands will always start fresh on the current year. If you'd like to tie it to the previous year instead, so it continues from where it left off the previous year, try the following:

=OR(MOD(B3-DATE(YEAR(B3)-1,1,0)-1,14)<5,MOD(B3-DATE(YEAR(B3)-1,1,0)-1,14)=10,MOD(B3-DATE(YEAR(B3)-1,1,0)-1,14)=11)

The new version adds "-1" after each YEAR(B3) clause, so even when your calendar says January 2017, the CF rule should regard that as an extension of 2016. To continue on into 2018, you'd change the "-1" additions to "-2" and so on. That's the easiest adjustment that comes to mind.

Alternatively, you could adjust the MOD results. Currently, the formula basically says "if the number of days since Jan. 1st 2016, less one, is divided by 14, and the remainder is less than 5, equal to 10, or equal to 11, then highlight the cell". You could use the same basic format but change the OR clauses to highlight different dates. For your purposes, though, it sounds like the first solution would be much easier.

I'm afraid I don't know of a color scaling solution, but in response to your second question: the formula is rooted into the date in cell B3, which is tied to the selected year, so the formula as it stands will always start fresh on the current year. If you'd like to tie it to the previous year instead, so it continues from where it left off the previous year, try the following:

=OR(MOD(B3-DATE(YEAR(B3)-1,1,0)-1,14)<5,MOD(B3-DATE(YEAR(B3)-1,1,0)-1,14)=10,MOD(B3-DATE(YEAR(B3)-1,1,0)-1,14)=11)

The new version adds "-1" after each YEAR(B3) clause, so even when your calendar says January 2017, the CF rule should regard that as an extension of 2016. To continue on into 2018, you'd change the "-1" additions to "-2" and so on. That's the easiest adjustment that comes to mind.

Alternatively, you could adjust the MOD results. Currently, the formula basically says "if the number of days since Jan. 1st 2016, less one, is divided by 14, and the remainder is less than 5, equal to 10, or equal to 11, then highlight the cell". You could use the same basic format but change the OR clauses to highlight different dates. For your purposes, though, it sounds like the first solution would be much easier.

Originally Posted by cantosh

I'm afraid I don't know of a color scaling solution, but in response to your second question: the formula is rooted into the date in cell B3, which is tied to the selected year, so the formula as it stands will always start fresh on the current year. If you'd like to tie it to the previous year instead, so it continues from where it left off the previous year, try the following:

=OR(MOD(B3-DATE(YEAR(B3)-1,1,0)-1,14)<5,MOD(B3-DATE(YEAR(B3)-1,1,0)-1,14)=10,MOD(B3-DATE(YEAR(B3)-1,1,0)-1,14)=11)

The new version adds "-1" after each YEAR(B3) clause, so even when your calendar says January 2017, the CF rule should regard that as an extension of 2016. To continue on into 2018, you'd change the "-1" additions to "-2" and so on. That's the easiest adjustment that comes to mind.

Alternatively, you could adjust the MOD results. Currently, the formula basically says "if the number of days since Jan. 1st 2016, less one, is divided by 14, and the remainder is less than 5, equal to 10, or equal to 11, then highlight the cell". You could use the same basic format but change the OR clauses to highlight different dates. For your purposes, though, it sounds like the first solution would be much easier.

Excellent, thanks.