VLOOKUP/MACRO using dates for NXT WK

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 are:
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!

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 are:
> 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
>
>

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 are:
> > 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
> >
> >