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"
soft.com wrote in message
...
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"
soft.com wrote in message
...
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"
soft.com wrote in message
...
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. FormatCellsNumber tabDate. Select the
date style of your choice.


--
Biff
Microsoft Excel MVP


"Capture a variable date & use with text"
soft.com wrote in message
...
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"
soft.com wrote in
message
...
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