Display "this week" column headers w/date & day of week?

[Guest]

What I'm trying to create is a sort of calendar that has the 7 days of the
week as the column headers. I want the headers to display the day of the week
and the date depending on what week it is. So if it's the week of September
9th, I want A1 to show 9/9/2007 - Sunday, A2 would be 9/10/2007 - Monday,
etc. Then, the following week I want the headers to change so that A1 is now
9/16/2007 - Sunday.

Of course, this is simple to do manually but I don't want to have to change
the headers every Sunday... is this even possible?

 

[Guest]

Hi Ivan,

If you enter the date in A1 and then in B1 you enter the formula = A1+1 and
drag that formula across to the end of the week then you only have to change
the date in cell A1 each week.

Regards,

OssieMac

 

[Guest]

Try this:

In A1: =TODAY()-MOD(TODAY()-1,7)
In A2: =A1+1
copy down to A7

 

[Pete_UK]

Put this formula in A1:

=TODAY()-WEEKDAY(TODAY())+1

and apply a custom format to the cell of "mm/dd/yyy - dddd" (without
the quotes). Then in B1 enter the formula:

=A1+1

The format should change to be the same as A1, but if it doesn't then
use the Format Painter to copy the format from A1 to B1. Then just
copy B1 into C1:G1, and then you have your headings, which will change
every Sunday.

I was a bit confused when you said A2 for Monday, but I think this is
what you want.

Hope this helps.

Pete

 

[Rick Rothstein \(MVP - VB\)]

Put this formula in A1:

=TODAY()-WEEKDAY(TODAY())+1

and apply a custom format to the cell of "mm/dd/yyy - dddd" (without
the quotes). Then in B1 enter the formula:

=A1+1

Or, put this formula...

=TODAY()-WEEKDAY(TODAY())+ROWS($1:1)

in A1 and just copy it down.

Rick

 

[Pete_UK]

The OP said he wanted the dates as column headers (twice), and then
referred to A1 and A2 as examples - I presume he wants them to go
across rather than down.

Although there are advantages to having just one formula, I think
having =A1+1 six times is easier.

Pete

 

[Rick Rothstein \(MVP - VB\)]

The OP said he wanted the dates as column headers (twice),

and then referred to A1 and A2 as examples - I presume he
wants them to go across rather than down.

For the record, the single formula for column headers would be....

=TODAY()-WEEKDAY(TODAY())+COLUMNS($A:A)

copied across.

Rick

 

[Guest]

You could also enter the suggested formula on 2 rows and format each how you
want to see it ie once as dd/mmm/yy and once as dddd, so that you get the
date in one cell and the day in another

 

[Pete_UK]

Or you could just put the words "Sunday", "Monday", etc in the second
row - they are not going to change.

Pete

 

[Rick Rothstein \(MVP - VB\)]

LOL

I'm not laughing at Richard; but, rather, how easy it is to overlook the
obvious... I think we have all been guilty of that at one time or another.

For the OP... rather than typing in each day name individually, just put the
word Sunday in your first column's first cell and drag it across to place
the other day names into the adjacent cells.

Rick