Availability calendar using conditional formatting

[Tony Vargo]

Hi all.

I'm hoping someone can help me out with a formatting issue I’m having please?

I’m trying to create an availability calendar, similar to what you would see if you were booking a restaurant online I guess. It’s so my team can see how many hours are available to take as time off, as they are not permitted to see the main holiday sheet.

I have no problem linking the hours available from one sheet to the other, but I’m struggling with formatting the cells. I’ve tried several ways including conditional formatting, but I can’t seem to get it right.

I’ve attached the sheet so you can see what I’m trying to achieve. Any help would be appreciated

Thanks a lot,

Tony

[Pete_UK]

The rules that you have set up for March (based on 8, 16 and 24 hours) don't seem to tie in with your Key. Which is the correct interpretation?

Pete

[Tony Vargo]

Hi Pete, thanks for replying.

Probably just an error on my part, it’s been bugging me so much I wasn't concentrating

Basically, anything 24 hours or above is red (no time off available), anything between 16 and 24 hours is amber (meaning 8 hours available to take), and anything under 16 is green (meaning more than 8 hours available).

Thanks,

Tony

[Pete_UK]

First of all, you should select the cells to which you have already applied CF to in March, then click on Conditional Formatting | Clear Rules | Clear rules from selected cells.

Then select cell W8 and click on Conditional Formatting | New rule | Use a formula... , then put this formula in the dialogue box:

=W8>=24

Click on the Format button | Fill tab and choose Red, then OK your way out.

Repeat, but use this formula:

=AND(W8>=16,W8<24)

and choose amber as the fill colour. Repeat once more, using this formula:

=AND(W8<16,W8<>"")

and choose green for the colour.

After exiting the CF dialogue, you can select cell W8 and double-click the Format Painter icon - this enables you to apply those conditions to other cells until you press the < Esc > key, and as all the references to W8 are relative, they will automatically change for other cells. So, you can click on the cells X8 (and also on T8, U8, V8 in turn). Press the < Esc > key, then select the cells T8:X8, then double-click on the Format Painter icon again, the click on T10, T12, T14 and T16 to apply those CF conditions to the whole month, and press < Esc > again.

You can then repeat this for all the other months.

Hope this helps.

Pete

[Tony Vargo]

Pete that's amazing! Thanks a lot, I really appreciate it

I had no idea what that format painter icon did!

Thanks,

Tony