How capture a date variable within a text cell?

[Capture a variable date & use with text]

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

 

[T. Valko]

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"

 

[Capture a variable date & use with text]

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

 

[Capture a variable date & use with text]

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

 

[T. Valko]

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"