Date formatiing - using code to block months together

  • Thread starter Thread starter Roger on Excel
  • Start date Start date
R

Roger on Excel

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
 
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)?
 
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
 
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 ***************
 
Hi Rick,

Thanks for looking into this

There appears to be a word wrap problem with the code.

Can you take a look?

Thanks,

Roger
 
Rick,

Excelent code !!!

I got it to work - works great - just what i was looking for.

By the way - how would i make it update itself automatically when the
spreadsheet opens?

All the best,

Roger
 
Before I can answer that question, you will need to tell me how your dates
are handled. That first date... how it it entered into the cell? Do you
enter the date manually? Do you have a formula in it? If you are using a
formula, what it that formula?
 
Open up the workbook code window (double click the ThisWorkbook item in the
project window) and copy/paste this code into it...

Private Sub Workbook_Open()
Sheet1.AddMergedMonthNames
End Sub
 
Rick - thanks for the code, but it doesnt seem to like the "Sheet1" bit

The code is associated with the sheet "Project Plan". I tried substituting
it but it didnt work.

What do you think?

Rog
 
Whoops! Sorry, that should have been...

Worksheets("Sheet1").AddMergedMonthNames

where you would substitute the name of your worksheet (in quotes) in place
of the example "Sheet1" that I used.
 
Hi Rick,

Thanks so much for all your help. I am learning so much.

I wish you all the best - have a great week,

Roger
 
Back
Top