Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting date ranges
A1:G12 is a calendar (VBA code from microsoft) / A15:A25 is name field /
B15:B25 is start date field / C15:C25 is End Date field. 1. I want the dates between start/end to highlight if an employee wants those as vacation days. (i can figure this out if only one employee takes a vacation but not for several employees within the same month ie max 3 conditional formats 2. Can i have multiple highlights if employee vacations have crossover dates for ex: mike jan 2 - jan 5 and todd jan 4 - jan 8. if you think something else would benefit me more pls let me know thanks in advance |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting date ranges
hey daddylonglegs, the first two conditions work great! its the 3rd
condition.... it wants to highlight the whole calendar even if there is no 3rd employee for the vacation, also only certain rows contain the dates but i noticed if you only select the rows the condition dont work but selecting the whole area it works fine. i found the source code... maybe that will help with the visuals a little better http://support.microsoft.com/default...&Product=xlw2K thanks in advance! "daddylonglegs" wrote: Do the cells A1:G12 contain actual dates? If so you could do the following: Select A1:G12 and apply the following conditions with "formula is" option condition 1 =SUM((A1=$B$15:$B$25)*(A1<=$C$15:$C$25))=1 format as desired, e.g. green condition 2 =SUM((A1=$B$15:$B$25)*(A1<=$C$15:$C$25))=2 format as desired, e.g. amber condition 3 =SUM((A1=$B$15:$B$25)*(A1<=$C$15:$C$25))2 format as desired, e.g. red This will give you one colour for days with a single person on vacation, another colour for two people on vacation and a third colour for 3 or more on vacation. Of course it also formats weekends but that can be altered if you wish..... "arkmpr" wrote: A1:G12 is a calendar (VBA code from microsoft) / A15:A25 is name field / B15:B25 is start date field / C15:C25 is End Date field. 1. I want the dates between start/end to highlight if an employee wants those as vacation days. (i can figure this out if only one employee takes a vacation but not for several employees within the same month ie max 3 conditional formats 2. Can i have multiple highlights if employee vacations have crossover dates for ex: mike jan 2 - jan 5 and todd jan 4 - jan 8. if you think something else would benefit me more pls let me know thanks in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting of rows | Excel Discussion (Misc queries) | |||
Conditional Format based on date ranges | Excel Worksheet Functions | |||
Conditional Formatting when inserting a row | Excel Worksheet Functions | |||
Conditional Formatting Cell < Today() | Excel Worksheet Functions | |||
Ranges within Conditional Formatting | Excel Discussion (Misc queries) |