conditional formatting weeks

hey all,

i'm trying to find a way to format a list of dates that can highlight those that are coming up next week. what i have is a list of work that needs done and what date those jobs fall on, i'd like to have everything that comes up next week a different color so it's easier for the crew to seperate whats happening this week and whats on deck for next. any ideas? thank you

Is it possible to break it up by week number? Like say, we're in week 4 now - could it show everything that started before this week highlighted one color, then next Monday at the start of week 5 have that another color?

By the way, thanks for the quick reply

You can't use Analysis ToolPak functions in conditional formatting so, assuming you don't have Excel 2007 you'd have to use WEEKNUM formula on the worksheet and then reference the result...

..or directly....for dates before this Monday use this as condition 1

=A1<TODAY()-WEEKDAY(TODAY(),3)

for dates this week use this for condition 2

=A1<TODAY()-WEEKDAY(TODAY(),3)+7

and then for any later dates condition 3

=A1>=TODAY()-WEEKDAY(TODAY(),3)+7

that works perfect; thank you!