• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel - How to conditionally format 2 different category of leave

H

Hazra Hadee

Member
Hello,

I am working on a leave notification form. I have seen the example Chandoo had provided and it was a great help. I will post the form once I complete it if others are interested to see it.

Now to my question:

I have to track the work days taken for the various categories of leave. So the form has

1. Business leave
Start date End date
leave category 1 1-Jul-2015 5-Jul-2015
leave category 2 6-Jul-2015 15-Jul-2015
leave category 3 16-Jul-2015 20-Jul-2015

2. Personal leave

Start date End date
leave category 1 1-Jul-2015 5-Jul-2015
leave category 2 6-Jul-2015 15-Jul-2015
leave category 3 16-Jul-2015 20-Jul-2015

I have a calendar at the bottom of the form and it highlights the leave taken from start date to end date and highlights working day, weekend and holidays. I have got the first date entered and the last day entered, however getting confused with the formating. i want the business leave highlighted in orange and the personal one in red.

However if I want to highlight the business and personal leave in 2 different color, how do I do this. I am getting stumped. I know I am missing something but cannot put my finger on it. Please help. I am attaching the form I am working on for better understanding.

Thank you
 

Attachments

See if attached meets your need.

I had to add helper cells (AT58:AU69)
Added Conditional formatting for Weekends and Dates in Month that does not exist (Ex: Feb 30,2016).
 

Attachments

Chihiro said:
See if attached meets your need.

I had to add helper cells (AT58:AU69)
Added Conditional formatting for Weekends and Dates in Month that does not exist (Ex: Feb 30,2016).
Click to expand...


Hi Chihiro,

One more question on the weekday. Currently the weekday function is highlighting Saturday and Sunday as weekends whereas I need Friday and Saturday as weekend. how to I get around this. Thank you in advance.
 
Hazra Hadee said:
Hi Chihiro,

One more question on the weekday. Currently the weekday function is highlighting Saturday and Sunday as weekends whereas I need Friday and Saturday as weekend. how to I get around this. Thank you in advance.
Click to expand...


Hi Chihiro,

Sorry, I figured it out. I put 0 to start week with sunday and ended it with >5

thank you
 
Hello

There seems to be a problem that I am unable to resolve. The file that you sent me, if I try to put in dates e.g starting in Jul and ending in Aug, the calendar doesn't display it correctly.

Also there is a new requirement to have weekend availability to be highlighted in the business color coding. They can put in the dates for the weekend if they are available. I am reattaching the form. Please help.
 

Attachments

You must log in or register to reply here.
Back
Top