day I have a row of dates which start from =TODAY() and increment by 1 along
the
row giving a sequential row of dates.

My question is as follows:

I would like to automatically format the row below the dates to show the
blocks of months for the day date row above- January, February etc

The day date row of course updates each day so i would like the month row to
reflect this.

Is there anyway to do this?

Thanks,

Roger

Hi Roger

Copy row 1 to row 2
Format row 2 with FormatCellsNumberCustommmmm

--
Regards
Roger Govier

"Roger on Excel" wrote in message
...
day I have a row of dates which start from =TODAY() and increment by 1
along
the
row giving a sequential row of dates.

My question is as follows:

I would like to automatically format the row below the dates to show the
blocks of months for the day date row above- January, February etc

The day date row of course updates each day so i would like the month row
to
reflect this.

Is there anyway to do this?

Thanks,

Roger

Hi Roger,

Thanks for this.

is there a way to have blocks of months - the format you give works, but the
row of date below the days has the month in every cell.

I would like a 31 day block for Jan and a 30 day block for April for example.

Best regards,

Roger

"Roger Govier" wrote:

Hi Roger

Copy row 1 to row 2
Format row 2 with FormatCellsNumberCustommmmm

--
Regards
Roger Govier

"Roger on Excel" wrote in message
...
day I have a row of dates which start from =TODAY() and increment by 1
along
the
row giving a sequential row of dates.

My question is as follows:

I would like to automatically format the row below the dates to show the
blocks of months for the day date row above- January, February etc

The day date row of course updates each day so i would like the month row
to
reflect this.

Is there anyway to do this?

Thanks,

Roger

I'm not entirely sure I understand what you are looking for. Let's look at a
restricted set of columns. Put =TODAY() in A1 and =TODAY()+1 in B1 and copy
the across to D1 (of course, you would do this as far across as necessary).
As I understand it, that is what you have started with. Now, in these
columns on Row 2 you want the month names for the dates in Row 1, is that
correct? So, since today is August 30, A1 thru D1 have 8/30/2008, 8/31/2008,
9/1/2008 and 9/2/2008 in them. A1 thru D2 have August, August, September and
September in them. What is it that you want to show under these (that is
what I am having trouble understanding from your "block" reference)?

--
Rick (MVP - Excel)


"Roger on Excel" wrote in message
...
Hi Roger,

Thanks for this.

is there a way to have blocks of months - the format you give works, but
the
row of date below the days has the month in every cell.

I would like a 31 day block for Jan and a 30 day block for April for
example.

Best regards,

Roger

"Roger Govier" wrote:

Hi Roger

Copy row 1 to row 2
Format row 2 with FormatCellsNumberCustommmmm

--
Regards
Roger Govier

"Roger on Excel" wrote in
message
...
day I have a row of dates which start from =TODAY() and increment by 1
along
the
row giving a sequential row of dates.

My question is as follows:

I would like to automatically format the row below the dates to show
the
blocks of months for the day date row above- January, February etc

The day date row of course updates each day so i would like the month
row
to
reflect this.

Is there anyway to do this?

Thanks,

Roger

Hi Rick,

For the month row I want to display just a single August or September label
under that row of day dates. But I need it to update as the dates change

So, for September, the month row will be a single horizontally formatted
merged cell with just "September" below the vertically formatted row of days
for that month. This would be the same for subsequent months of course. I
dont want to see the month under each and every date

How would one accomplish this?

Roger



"Rick Rothstein" wrote:

I'm not entirely sure I understand what you are looking for. Let's look at a
restricted set of columns. Put =TODAY() in A1 and =TODAY()+1 in B1 and copy
the across to D1 (of course, you would do this as far across as necessary).
As I understand it, that is what you have started with. Now, in these
columns on Row 2 you want the month names for the dates in Row 1, is that
correct? So, since today is August 30, A1 thru D1 have 8/30/2008, 8/31/2008,
9/1/2008 and 9/2/2008 in them. A1 thru D2 have August, August, September and
September in them. What is it that you want to show under these (that is
what I am having trouble understanding from your "block" reference)?

--
Rick (MVP - Excel)


"Roger on Excel" wrote in message
...
Hi Roger,

Thanks for this.

is there a way to have blocks of months - the format you give works, but
the
row of date below the days has the month in every cell.

I would like a 31 day block for Jan and a 30 day block for April for
example.

Best regards,

Roger

"Roger Govier" wrote:

Hi Roger

Copy row 1 to row 2
Format row 2 with FormatCellsNumberCustommmmm

--
Regards
Roger Govier

"Roger on Excel" wrote in
message
...
day I have a row of dates which start from =TODAY() and increment by 1
along
the
row giving a sequential row of dates.

My question is as follows:

I would like to automatically format the row below the dates to show
the
blocks of months for the day date row above- January, February etc

The day date row of course updates each day so i would like the month
row
to
reflect this.

Is there anyway to do this?

Thanks,

Roger

Okay, I needed to make a couple of assumptions. First, I assumed that you
have a real date in the first column of your date row and all the other
dates in that row are generated by a formula that adds one to the previous
column. This way, changing a single cell (the first column of the date row)
will change all the other dates automatically; so I keyed the solution onto
your changing that cell only. Second, I assumed you will *never* have a date
(in the date row) in the *last* column in the spreadsheet. Copy/Paste all
the code below into the worksheet's code window where you want this
functionality (right click the worksheet's tab, select View Code in order to
bring up the proper window). Immediately after you paste the code into the
code window, change the two Const statements at the very beginning of the
code to reflect your actual worksheet layout. The DateRow constant is the
row where the dates are located and the StartColumn is the column number of
the first date in that row. After you have done that, go to the worksheet
and enter a date into that first date cell.... you should see the merged
areas you requested (plus I placed a border around them).

'*************** START OF CODE ***************
Private Const DateRow As Long = 2
Private Const StartColumn As Long = 3

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Cells(DateRow, StartColumn).Address Then
AddMergedMonthNames
End If
End Sub

Sub AddMergedMonthNames()
Dim X As Long
Dim LastColumn As Long
Dim CurrentMonth As Long
Dim CurrentColumn As Long
LastColumn = Cells(DateRow, Columns.Count).End(xlToLeft).Column
Cells(DateRow, LastColumn + 1).Value = _
Cells(DateRow, LastColumn).Value + 32
CurrentColumn = StartColumn
CurrentMonth = Month(Cells(DateRow, CurrentColumn).Value)
Rows(DateRow + 1).Clear
For X = StartColumn + 1 To LastColumn + 1
If Month(Cells(DateRow, X).Value) < CurrentMonth Then
Cells(DateRow + 1, CurrentColumn).Value = MonthName(CurrentMonth)
Cells(DateRow + 1, CurrentColumn).Resize(1, X - CurrentColumn).Merge
True
Cells(DateRow + 1, CurrentColumn).MergeArea.BorderAround xlContinuous,
xlMedium
CurrentMonth = Month(Cells(DateRow, X).Value)
CurrentColumn = X
End If
Next
Cells(DateRow, LastColumn + 1).Clear
End Sub
'*************** END OF CODE ***************

--
Rick (MVP - Excel)


"Roger on Excel" wrote in message
...
Hi Rick,

For the month row I want to display just a single August or September
label
under that row of day dates. But I need it to update as the dates change

So, for September, the month row will be a single horizontally formatted
merged cell with just "September" below the vertically formatted row of
days
for that month. This would be the same for subsequent months of course. I
dont want to see the month under each and every date

How would one accomplish this?

Roger



"Rick Rothstein" wrote:

I'm not entirely sure I understand what you are looking for. Let's look
at a
restricted set of columns. Put =TODAY() in A1 and =TODAY()+1 in B1 and
copy
the across to D1 (of course, you would do this as far across as
necessary).
As I understand it, that is what you have started with. Now, in these
columns on Row 2 you want the month names for the dates in Row 1, is that
correct? So, since today is August 30, A1 thru D1 have 8/30/2008,
8/31/2008,
9/1/2008 and 9/2/2008 in them. A1 thru D2 have August, August, September
and
September in them. What is it that you want to show under these (that is
what I am having trouble understanding from your "block" reference)?

--
Rick (MVP - Excel)


"Roger on Excel" wrote in
message
...
Hi Roger,

Thanks for this.

is there a way to have blocks of months - the format you give works,
but
the
row of date below the days has the month in every cell.

I would like a 31 day block for Jan and a 30 day block for April for
example.

Best regards,

Roger

"Roger Govier" wrote:

Hi Roger

Copy row 1 to row 2
Format row 2 with FormatCellsNumberCustommmmm

--
Regards
Roger Govier

"Roger on Excel" wrote in
message
...
day I have a row of dates which start from =TODAY() and increment by
1
along
the
row giving a sequential row of dates.

My question is as follows:

I would like to automatically format the row below the dates to show
the
blocks of months for the day date row above- January, February etc

The day date row of course updates each day so i would like the
month
row
to
reflect this.

Is there anyway to do this?

Thanks,

Roger