• 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.

Help with formula and conditonal formatting in excel

S

Sheeba

New Member
Good Day Chandoo!


I am a beginner with excel and I've been trying to do an excel file for annual leave of employees with start dates and end dates of vacation in two separate columns and from the third column onwards I have dates of a calendar year. In this sheet, I would like to highlight the range of cells in the calendar at the right side against the start and end date. I tried putting this formula; IF(AND(C$3>$A8,C$3<$B3),"x","") but didn't work :(. I wanted to later change the x into blue colour so that the period of leave is highlighted in each row w.r.t the dates mentioned in columns with the start and end dates.


Please help me and let me know how I can make it work.


Many thanks,

Sheeba
 
Sheeba


Firstly, Welcome to the Chandoo.org forums


The biggest mistake I see in these style problems is that people don't use Dates as Headings they use Text. If your dates are all real Dates, that is Numbers, you will probably be half way there.


Can you post a sample of your file for us to review?

Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Hi Sheeba ,


Let us assume the following :


1. Your employees' names are in column A , starting with cell A2 , and going down , to A3 , A4 ,...


2. For each employee , the start of his / her vacation is in column B , and the end of the vacation is in column C.


3. The dates are in row 1 , starting from cell D1.


In cell D2 , against Conditional Formatting , put in the following formula :


=AND(D$1>=$B2,D$1<=$C2)


Select the colour of your choice for formatting the cell.


Using the Format Painter , copy this format to as many rows and columns as you want.


Narayan
 
Many thanks Hui!


I've already put my file in the drop box (annual leave), appreciate if you could look at it and advise.


Maybe it's a dumb error :(.


Tx

Sheeba
 
Hi Narayan,


I tried that earlier as well but it didn't work. Like Hui said, I guess that it must be with the style or some error like that that is hindering this process.


Well, I am going to try again.


Tx

Sheeba
 
Hi Hui,


I need your email address to let you have access to my file in the dropbox right?


Please send, so that I can add on.


Many thanks,

Sheeba
 
Hi Sheeba,


Please refer to sticky post on the forum for uploading a file...


for Hui ID, click on Excel Ninja below his name....in the end you can find his ID..
 
Ooops Hui,


here it is again...


https://www.dropbox.com/s/wr1wh81y54fttsq/annual%20leave.xlsx?m


Tx

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