Hi guys,
i need help with the attached spreadsheet. It's a monthly calendar that i have made but i have problems in conditional formatting in regarding of highlighting the holidays of the month. i.e on the attached spreadsheet on column AT-AU is the dates of all declared holiday, when i click refresh button, it will highlight the date based on the holidays lookup all employee will highlight downwards.
hope anyone here can help me.
thanks,
Albert
Maybe you could use this for CF:
=COUNTIF($AT$8:$AT$19;DATE(YEAR($B$3);MONTH($B$3);E$4))>0
Also, in B3 is better to use TODAY() function for current date instead of NOW() because NOW() include current date and time while TODAY current date
Last edited by zbor; 07-05-2010 at 02:14 AM.
Hello,
I think this might work out for you if you get rit of the range part.
Marcel
A B C D
1 Vacation
2 DATE DATE NAME DESCRIPTION
3 31-Jul-2010 21-Aug-2010 Jeremy Summer vacation
4 21-Aug-2010 21-Aug-2010 Sunny Free day
5 21-Aug-2010 21-Aug-2010 Bart Free day
6 27-Dec-2010 31-Dec-2010 Jeremy Christmas vacation
7 29-Dec-2010 31-Dec-2010 Sunny Christmas vacation
Named Ranges:
VacationStart: A3-A99
VacationEnd : B3-B99
Names : C3-C99
=IF(ISREF(INDEX(VacationEnd,MATCH(E8,VacationStart, 1),1)),IF(E8<=INDEX(VacationEnd,MATCH(E8,VacationStart, 1),1), TRUE, FALSE), FALSE)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks