| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need task list that includes date and days + or -
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. -- Thanks, Sharon |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need task list that includes date and days + or -
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) "SharonT" wrote in message ... 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. -- Thanks, Sharon |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need task list that includes date and days + or -
Thanks Bob - I'll give it a try. Got to tell you my effort was far more
complicated. Have to start thinking simple! -- Thanks, Sharon "Bob Phillips" wrote: 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) "SharonT" wrote in message ... 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. -- Thanks, Sharon |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need task list that includes date and days + or -
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 "Bob Phillips" wrote: 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) "SharonT" wrote in message ... 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. -- Thanks, Sharon |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need task list that includes date and days + or -
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!!! -- Thanks, Sharon "SharonT" wrote: 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 "Bob Phillips" wrote: 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) "SharonT" wrote in message ... 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. -- Thanks, Sharon |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Date and Days list problem | Excel Discussion (Misc queries) | |||
| How can I count up values in a list that includes duplicates? | Excel Discussion (Misc queries) | |||
| How to choose from a list that includes blanks? | Excel Discussion (Misc queries) | |||
| Drop down list includes hyperlink | Excel Discussion (Misc queries) | |||
| Cannot protect worksheet if it includes a list | Excel Worksheet Functions | |||
Linear Mode
