I am creating a work schedule and need to be able to pick up a date and place
it within a report header "Week Ending May 15, 2008". The date is always
Thursday of next week but I don't always run the report on the same day so I
need to prompt for the date when I actually run it. I also need to use that
date for column headers for each day of the week. I would put the date into
the right most column to start with, then subtract one to get the date for
Wednesday which would be the next column to the left, then subtract one to
get the date for Tuesday which would be the next column to the left, and so
on.

Here is a very crude of example of what I need ...

Week Ending May 15, 2008

Friday Saturday Sunday Monday Tuesday Wednesday
Thursday
05/09/2008 05/10/2008 05/11/2008 05/12/2008 05/13/2008 05/14/2008

05/15/2008

Thanks for any help and suggestions

I don't understand the significance of:

>I need to prompt for the date when I actually run it.

Prompt for what date, the date you make out the schedule? Why do you need a
prompt? You can just enter that date in a cell.

What does that date have to do with it? Do you need that date to calculate
next Thursday's date?

This formula will *always* return the date of next weeks Thursday (based on
a Mon through Sun week)

=TODAY()-WEEKDAY(NOW(),3)+10

Let's assume you want the dates in A4:G4 with G4 being next Thursday's date.
Enter the above formula cell G4. Then enter this formula in cell A4 and copy
across to cell F4:

=$G4-COLUMNS(A4:$F4)

To get your header: Week Ending May 15, 2008

="Week Ending "&TEXT(G4,"mmmm d, yyyy")


--
Biff
Microsoft Excel MVP


"Capture a variable date & use with text"
<(E-Mail Removed)> wrote in message
news7278E97-5DE4-48B8-80AB-(E-Mail Removed)...
>I am creating a work schedule and need to be able to pick up a date and
>place
> it within a report header "Week Ending May 15, 2008". The date is always
> Thursday of next week but I don't always run the report on the same day so
> I
> need to prompt for the date when I actually run it. I also need to use
> that
> date for column headers for each day of the week. I would put the date
> into
> the right most column to start with, then subtract one to get the date for
> Wednesday which would be the next column to the left, then subtract one to
> get the date for Tuesday which would be the next column to the left, and
> so
> on.
>
> Here is a very crude of example of what I need ...
>
> Week Ending May 15, 2008
>
> Friday Saturday Sunday Monday Tuesday Wednesday
> Thursday
> 05/09/2008 05/10/2008 05/11/2008 05/12/2008 05/13/2008 05/14/2008
>
> 05/15/2008
>
> Thanks for any help and suggestions

One format question tho...

How do I get the date format with the TODAY()-WEEKDAY(NOW(),3)+10 to be
mm/dd/yy? Where/how do I insert the "mm/dd/yy" within that line..thanks again

"T. Valko" wrote:

> I don't understand the significance of:
>
> >I need to prompt for the date when I actually run it.
>
> Prompt for what date, the date you make out the schedule? Why do you need a
> prompt? You can just enter that date in a cell.
>
> What does that date have to do with it? Do you need that date to calculate
> next Thursday's date?
>
> This formula will *always* return the date of next weeks Thursday (based on
> a Mon through Sun week)
>
> =TODAY()-WEEKDAY(NOW(),3)+10
>
> Let's assume you want the dates in A4:G4 with G4 being next Thursday's date.
> Enter the above formula cell G4. Then enter this formula in cell A4 and copy
> across to cell F4:
>
> =$G4-COLUMNS(A4:$F4)
>
> To get your header: Week Ending May 15, 2008
>
> ="Week Ending "&TEXT(G4,"mmmm d, yyyy")
>
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "Capture a variable date & use with text"
> <(E-Mail Removed)> wrote in message
> news7278E97-5DE4-48B8-80AB-(E-Mail Removed)...
> >I am creating a work schedule and need to be able to pick up a date and
> >place
> > it within a report header "Week Ending May 15, 2008". The date is always
> > Thursday of next week but I don't always run the report on the same day so
> > I
> > need to prompt for the date when I actually run it. I also need to use
> > that
> > date for column headers for each day of the week. I would put the date
> > into
> > the right most column to start with, then subtract one to get the date for
> > Wednesday which would be the next column to the left, then subtract one to
> > get the date for Tuesday which would be the next column to the left, and
> > so
> > on.
> >
> > Here is a very crude of example of what I need ...
> >
> > Week Ending May 15, 2008
> >
> > Friday Saturday Sunday Monday Tuesday Wednesday
> > Thursday
> > 05/09/2008 05/10/2008 05/11/2008 05/12/2008 05/13/2008 05/14/2008
> >
> > 05/15/2008
> >
> > Thanks for any help and suggestions
>
>
>

Whoops...must be all the rum I drank today!

I need the formula for the column headers to be mm/dd/yy also! Again many
thanks for our help

"Capture a variable date & use with text" wrote:

> One format question tho...
>
> How do I get the date format with the TODAY()-WEEKDAY(NOW(),3)+10 to be
> mm/dd/yy? Where/how do I insert the "mm/dd/yy" within that line..thanks again
>
> "T. Valko" wrote:
>
> > I don't understand the significance of:
> >
> > >I need to prompt for the date when I actually run it.
> >
> > Prompt for what date, the date you make out the schedule? Why do you need a
> > prompt? You can just enter that date in a cell.
> >
> > What does that date have to do with it? Do you need that date to calculate
> > next Thursday's date?
> >
> > This formula will *always* return the date of next weeks Thursday (based on
> > a Mon through Sun week)
> >
> > =TODAY()-WEEKDAY(NOW(),3)+10
> >
> > Let's assume you want the dates in A4:G4 with G4 being next Thursday's date.
> > Enter the above formula cell G4. Then enter this formula in cell A4 and copy
> > across to cell F4:
> >
> > =$G4-COLUMNS(A4:$F4)
> >
> > To get your header: Week Ending May 15, 2008
> >
> > ="Week Ending "&TEXT(G4,"mmmm d, yyyy")
> >
> >
> > --
> > Biff
> > Microsoft Excel MVP
> >
> >
> > "Capture a variable date & use with text"
> > <(E-Mail Removed)> wrote in message
> > news7278E97-5DE4-48B8-80AB-(E-Mail Removed)...
> > >I am creating a work schedule and need to be able to pick up a date and
> > >place
> > > it within a report header "Week Ending May 15, 2008". The date is always
> > > Thursday of next week but I don't always run the report on the same day so
> > > I
> > > need to prompt for the date when I actually run it. I also need to use
> > > that
> > > date for column headers for each day of the week. I would put the date
> > > into
> > > the right most column to start with, then subtract one to get the date for
> > > Wednesday which would be the next column to the left, then subtract one to
> > > get the date for Tuesday which would be the next column to the left, and
> > > so
> > > on.
> > >
> > > Here is a very crude of example of what I need ...
> > >
> > > Week Ending May 15, 2008
> > >
> > > Friday Saturday Sunday Monday Tuesday Wednesday
> > > Thursday
> > > 05/09/2008 05/10/2008 05/11/2008 05/12/2008 05/13/2008 05/14/2008
> > >
> > > 05/15/2008
> > >
> > > Thanks for any help and suggestions
> >
> >
> >

Just format the date cells as Date. Format>Cells>Number tab>Date. Select the
date style of your choice.


--
Biff
Microsoft Excel MVP


"Capture a variable date & use with text"
<(E-Mail Removed)> wrote in message
news:3D362E39-C3C0-4F98-A2C5-(E-Mail Removed)...
> Whoops...must be all the rum I drank today!
>
> I need the formula for the column headers to be mm/dd/yy also! Again many
> thanks for our help
>
> "Capture a variable date & use with text" wrote:
>
>> One format question tho...
>>
>> How do I get the date format with the TODAY()-WEEKDAY(NOW(),3)+10 to be
>> mm/dd/yy? Where/how do I insert the "mm/dd/yy" within that line..thanks
>> again
>>
>> "T. Valko" wrote:
>>
>> > I don't understand the significance of:
>> >
>> > >I need to prompt for the date when I actually run it.
>> >
>> > Prompt for what date, the date you make out the schedule? Why do you
>> > need a
>> > prompt? You can just enter that date in a cell.
>> >
>> > What does that date have to do with it? Do you need that date to
>> > calculate
>> > next Thursday's date?
>> >
>> > This formula will *always* return the date of next weeks Thursday
>> > (based on
>> > a Mon through Sun week)
>> >
>> > =TODAY()-WEEKDAY(NOW(),3)+10
>> >
>> > Let's assume you want the dates in A4:G4 with G4 being next Thursday's
>> > date.
>> > Enter the above formula cell G4. Then enter this formula in cell A4 and
>> > copy
>> > across to cell F4:
>> >
>> > =$G4-COLUMNS(A4:$F4)
>> >
>> > To get your header: Week Ending May 15, 2008
>> >
>> > ="Week Ending "&TEXT(G4,"mmmm d, yyyy")
>> >
>> >
>> > --
>> > Biff
>> > Microsoft Excel MVP
>> >
>> >
>> > "Capture a variable date & use with text"
>> > <(E-Mail Removed)> wrote in
>> > message
>> > news7278E97-5DE4-48B8-80AB-(E-Mail Removed)...
>> > >I am creating a work schedule and need to be able to pick up a date
>> > >and
>> > >place
>> > > it within a report header "Week Ending May 15, 2008". The date is
>> > > always
>> > > Thursday of next week but I don't always run the report on the same
>> > > day so
>> > > I
>> > > need to prompt for the date when I actually run it. I also need to
>> > > use
>> > > that
>> > > date for column headers for each day of the week. I would put the
>> > > date
>> > > into
>> > > the right most column to start with, then subtract one to get the
>> > > date for
>> > > Wednesday which would be the next column to the left, then subtract
>> > > one to
>> > > get the date for Tuesday which would be the next column to the left,
>> > > and
>> > > so
>> > > on.
>> > >
>> > > Here is a very crude of example of what I need ...
>> > >
>> > > Week Ending May 15, 2008
>> > >
>> > > Friday Saturday Sunday Monday Tuesday
>> > > Wednesday
>> > > Thursday
>> > > 05/09/2008 05/10/2008 05/11/2008 05/12/2008 05/13/2008 05/14/2008
>> > >
>> > > 05/15/2008
>> > >
>> > > Thanks for any help and suggestions
>> >
>> >
>> >