Filling Column with date

[Guest]

I need some help. I have a spredsheet that displays three months of dates.

I place the start date in cell A5 like 1 Dec 04. Then starting in cloumn B5,
the Date is place in the cell. In cell B6, the day is insert.

EXAMPLE

A5 = 1 Dec 04 then
B5 = 1, and B6 = W for Wednesday
C5 = 2, and C6 = T for Thursday
D5 = 3, and D6 = F for Friday

this sequence would need to continue until the through the third month. As
in the case of this example it would need to continue through 28 Feb 05. Can
anyone please provide me the VBA code to accomplishs this tasks.

Thanks

Bill

 

[Trevor Shuttleworth]

Bill

try something like:

Application.ScreenUpdating = False
' Range("A5") = "01-Dec-04"
Range("B5").FormulaR1C1 = "=RC[-1]"
Range("C5").FormulaR1C1 = "=RC[-1]+1"
Range("C5").AutoFill _
Destination:=Range("C5:CQ5")
Range("B6").FormulaR1C1 = "=R[-1]C[-1]"
Range("C6").FormulaR1C1 = "=RC[-1]+1"
Range("C6").AutoFill _
Destination:=Range("C6:CQ6")
Range("B5:CQ5").NumberFormat = "d"
Range("B6:CQ6").NumberFormat = "ddd"
Range("B5:CQ6").HorizontalAlignment = xlCenter
Application.ScreenUpdating = True

Bear in mind that the number of columns will depend on how many days there
are in each of the months. I don't think there is a format that just gets
the first letter of the day of the week, hence, Mon, Tue, Wed and so on.

Regards

Trevor

 

[Guest]

Bill,

Add a command button on your sheet and paste this code into the click
event. It checks for a valid date format, clears the old B5:CO6 area,
accounts for leap year and notes the days in a single letter configuration.
Give it a shot.

Roy

Dim StartMonth, Dayz, x As Integer
If IsDate(Range("A5")) = True Then
Range("B5:CO6").Select
Range("B5:CO6").ClearContents
Range("A5").Activate
StartMonth = Month(Range("A5"))
Select Case StartMonth
Case Is = 1, 12
Dayz = 90
Case Is = 2
Dayz = 89
Case Is = 3, 5, 6, 7, 8, 10, 11
Dayz = 92
Case Is = 4, 9
Dayz = 91
End Select
Select Case Month(Range("A5"))
Case Is = 12
x = (Year(Range("A5")) + 1) Mod 4
Case Is = 1, 2
x = Year(Range("A5")) Mod 4
End Select
If x = 0 Then Dayz = Dayz + 1
End If
Application.ScreenUpdating = False
For x = 1 To Dayz
Cells(5, x + 1).Value = Day(Range("A5") + x - 1)
Cells(5, x + 1).NumberFormat = "#"
Cells(6, x + 1).Value = Weekday(Range("A5") + x - 1)
Select Case Cells(6, x + 1).Value
Case Is = 1, 7
Cells(6, x + 1).Value = "S"
Case Is = 2
Cells(6, x + 1).Value = "M"
Case Is = 3, 5
Cells(6, x + 1).Value = "T"
Case Is = 4
Cells(6, x + 1).Value = "W"
Case Is = 6
Cells(6, x + 1).Value = "F"
End Select
Cells(6, x + 1).Value = Left(Cells(6, x + 1).Value, 1)
Next
Application.ScreenUpdating = True

 

[Guest]

You didn't mention if you always start on the first of the month. The earlier
version I posted assumed that was true and will add extra days on the end if
you do not start on the first. This version corrects that issue.

Roy

Private Sub CommandButton1_Click()
Dim StartMonth, Dayz, DayCode, x As Integer
Dim Letter As String
If IsDate(Range("A5")) = True Then
Range("B5:CO6").Select
Range("B5:CO6").ClearContents
Range("A5").Activate
StartMonth = Month(Range("A5"))
Select Case StartMonth
Case Is = 1, 12
Dayz = 90
Case Is = 2
Dayz = 89
Case Is = 3, 5, 6, 7, 8, 10, 11
Dayz = 92
Case Is = 4, 9
Dayz = 91
End Select
Dayz = Dayz - Day(Range("A5")) + 1 'just in case you don't start a
month on the 1st
Select Case Month(Range("A5"))
Case Is = 12
x = (Year(Range("A5")) + 1) Mod 4
Case Is = 1, 2
x = Year(Range("A5")) Mod 4
End Select
If x = 0 Then Dayz = Dayz + 1
End If
Application.ScreenUpdating = False
For x = 1 To Dayz
Cells(5, x + 1).Value = Day(Range("A5") + x - 1)
Cells(5, x + 1).NumberFormat = "#"
DayCode = Weekday(Range("A5") + x - 1)
Select Case DayCode
Case Is = 1, 7
Letter = "S"
Case Is = 2
Letter = "M"
Case Is = 3, 5
Letter = "T"
Case Is = 4
Letter = "W"
Case Is = 6
Letter = "F"
End Select
Cells(6, x + 1).Value = Letter
Next
Application.ScreenUpdating = True
End Sub

 

[Guest]

This works great with one exception if I put in a month where days equal 92
it gives me 93 days across the sheet. Can you help resolve this.

Thanks Bill

 

[Guest]

Great observation. It made it fairly easy to diagnose. The only way to get 93
days is with a leap year malfunction. Note that the variable x is unaffected
by the Cases in the Select Case(Month(Range("A5")) section in all of the 92
day months. But since x was never initialized, it inherited zero by default.
If we initialize it to anything else, we avoid the problem and the Case
statements will change x as needed. See the one line addition below. That
should do the trick. It's 1:40 am, so I'll let you test it and let me know.

Roy

This works great with one exception if I put in a month where days equal 92
it gives me 93 days across the sheet. Can you help resolve this.

Thanks Bill

x = 5 'we don't care what it is as long as it's not zero! OOPS!