Need task list that includes date and days + or -

[Guest]

I need to be able to design a task list that automatically identifies the
last four weekdays of the month as Day - 4, Day - 3, Day -2, Day - 1 and the
first four weekdays of the next month as Day +1, Day+2, Day+3, Day+4,
excluding weekends and holidays. For example,
cell A1 2/23/2007
cell A2 Day - 4
Cell B1 2/26/2007
Cell B2 Day - 3
Cell C1 2/27/2007
Cell C2 Day - 2
Cell D1 2/28/2007
Cell D2 Day - 1
Cell E1 3/1/2007
Cell E2 Day + 1, etc. The tasks would be listed in row three of each column.
....
I would prefer having these cells calculate automatically by referencing the
first day of the year. I think this is a calc using the functions WEEKDAYS
and EOMONTH but can't get it to work. Maybe this is a VBA candidate.

 

[Bob Phillips]

Put this is A1 if the month in question is May, month 5

=workday(DATE(YEAR(TODAY()),6,1),-4)

In A2, input

=workday(a1,1)

and copy down

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Thanks Bob - I'll give it a try. Got to tell you my effort was far more
complicated. Have to start thinking simple!

 

[Guest]

Bob - Sorry - I don't think I explained myself very well. Here's the result
I need:

Cols A B C D E
F G H
Row 1 1/26/07 1/29/07 1/30/07 1/31/07 2/1/07 2/2/07 2/5/07 2/6/07
Row 2 Day-4 Day-3 Day-2 Day-1 Day+1 Day+2 Day+3 Day+4
Row 3 Tasks Tasks Tasks Tasks Tasks Tasks Tasks
Tasks
-- I want the values in row 1 to recalculate based on a first of month date
entered in a cell outside this table. The idea is to have this table recalc
each month. The values in Row 1 cannot include weekend days or Holidays.

Thanks, Sharon

 

[Guest]

Got it - Just need to use =WORKDAY(StartDate,-4,Holidays) for Day-4 where
StartDate and Holidays are named ranges. The second arguement is changed for
each of the columns. The user just needs to enter the StartDate and the date
values change. Thanks a lot for putting me on the right track!!!