VLOOKUP/MACRO using dates for NXT WK
 

I am trying to have the dates of the business days for the NEXT week b
generated based on today's current date.

So today is 6/30/05, and next weeks business days a
M - none(holiday
T - 7/5/05
w - 7/6/05
TH- 7/7/05
F- 7/8/05

This would be pasted in 5 cells vertially and the dates would onl
change the first day of the workweek. So if i came on on Tuesday i
would give me next weeks dates.

Would the easiest thing to do just create static data and have
vloookup or create a formula?

I just need to be able to come in every monday and see the dates of th
next weeks. I tried it using EOMonth function with no luck. Any help i
appreciated. Thanks

--
dstoc
-----------------------------------------------------------------------
dstock's Profile: http://www.excelforum.com/member.php...fo&userid=2422
View this thread: http://www.excelforum.com/showthread.php?threadid=38354

VLOOKUP/MACRO using dates for NXT WK
 

Hi,

If I've understood correctly, this *should* be relatively simple ...

Use Excel's WEEKDAY sheet function and enter the following formula in cell
A1 (or the first cell of the first column).

=IF(WEEKDAY(TODAY(),3)0,TODAY()+7-WEEKDAY(TODAY(),3),TODAY())

(This tests to see if 'today' is after Monday and if so displays next Monday
or alternatively if not, 'last' Monday). Then in the adjacent cells/column
headings to the right you need only enter a formula to add 1 (day) to the
value of the cell to the immediate left. e.g. (Tuesday) B1: =A1+1, (Wed) C1:
=B1+1, (Thu) D1: = C1+1, (Fri) E1: = D1+1.

Make sure to format the range A1:B1 in the relevant date format desired e.g.
"ddd dd-mmm-yyy (or whatever).

National holidays are probably a another matter, but I'm sure that you could
incorporate a simple 1 column sorted list of these on a separate (hidden)
sheet and then include a test (using VLookup) in each of the 5 cell formulae.
e.g. D1: IF(C1+1=VLookup(C1+1,list,1,false),"none - holiday",C1+1).

Maybe not highly elegant and may need some tweaking, but should work and
hope this gives you the general idea. (Note also that you can change the
'base' of the WEEKDAY function if desired - Excel help on this function gives
you all the info).

HTH, Sean.

"dstock" wrote:


I am trying to have the dates of the business days for the NEXT week be
generated based on today's current date.

So today is 6/30/05, and next weeks business days a
M - none(holiday
T - 7/5/05
w - 7/6/05
TH- 7/7/05
F- 7/8/05

This would be pasted in 5 cells vertially and the dates would only
change the first day of the workweek. So if i came on on Tuesday it
would give me next weeks dates.

Would the easiest thing to do just create static data and have a
vloookup or create a formula?

I just need to be able to come in every monday and see the dates of the
next weeks. I tried it using EOMonth function with no luck. Any help is
appreciated. Thanks!


--
dstock
------------------------------------------------------------------------
dstock's Profile: http://www.excelforum.com/member.php...o&userid=24225
View this thread: http://www.excelforum.com/showthread...hreadid=383547

VLOOKUP/MACRO using dates for NXT WK
 

Oops, should read "format the range A1:E1 ...". (Forgive me ... :-))

Cheers, Sean.

"Sean Connolly" wrote:

Hi,

If I've understood correctly, this *should* be relatively simple ...

Use Excel's WEEKDAY sheet function and enter the following formula in cell
A1 (or the first cell of the first column).

=IF(WEEKDAY(TODAY(),3)0,TODAY()+7-WEEKDAY(TODAY(),3),TODAY())

(This tests to see if 'today' is after Monday and if so displays next Monday
or alternatively if not, 'last' Monday). Then in the adjacent cells/column
headings to the right you need only enter a formula to add 1 (day) to the
value of the cell to the immediate left. e.g. (Tuesday) B1: =A1+1, (Wed) C1:
=B1+1, (Thu) D1: = C1+1, (Fri) E1: = D1+1.

Make sure to format the range A1:B1 in the relevant date format desired e.g.
"ddd dd-mmm-yyy (or whatever).

National holidays are probably a another matter, but I'm sure that you could
incorporate a simple 1 column sorted list of these on a separate (hidden)
sheet and then include a test (using VLookup) in each of the 5 cell formulae.
e.g. D1: IF(C1+1=VLookup(C1+1,list,1,false),"none - holiday",C1+1).

Maybe not highly elegant and may need some tweaking, but should work and
hope this gives you the general idea. (Note also that you can change the
'base' of the WEEKDAY function if desired - Excel help on this function gives
you all the info).

HTH, Sean.

"dstock" wrote:


I am trying to have the dates of the business days for the NEXT week be
generated based on today's current date.

So today is 6/30/05, and next weeks business days a
M - none(holiday
T - 7/5/05
w - 7/6/05
TH- 7/7/05
F- 7/8/05

This would be pasted in 5 cells vertially and the dates would only
change the first day of the workweek. So if i came on on Tuesday it
would give me next weeks dates.

Would the easiest thing to do just create static data and have a
vloookup or create a formula?

I just need to be able to come in every monday and see the dates of the
next weeks. I tried it using EOMonth function with no luck. Any help is
appreciated. Thanks!


--
dstock
------------------------------------------------------------------------
dstock's Profile: http://www.excelforum.com/member.php...o&userid=24225
View this thread: http://www.excelforum.com/showthread...hreadid=383547