Dates and VBA Magic!

[tip2tail]

Hi all,

Looking to be able to do some fancy jiggery pokery in Excel to make my
life easier at work.

I want to be able to input (or select) a month Jan - Dec and have
Excell fill out rows downwards with the date and what day that is in
the current year. Each month in my company starts on the 15th and ends
on the 14th. An example thought process for this would be:

Select October from the list box (if possible) or input the name/number
(10) of the month. Again beside it input year(2005). Say this is in
C4.

The script in Excel would then say from row C6 onwards 15th oct 2005 is
a Sat- Place "15th Saturday" in C6, "16th Sunday" in C7, 17th = Monday
- Place "17th Monday" in row C8, "18th Tuesday" in C9 and so on until
the 14th November - "14th Monday".

Would this be possible to implement? You can e-mail me direct on
mark AT {my nickname - see above} DOT co DOT uk
if anyone wants more detail. Also post here. This is very important
to myself and my small company.

Thanks,

Mark D Young

 

[Alvin]

month input = b4
year input = c4

i created two lookup tables
- table for weekdays (1=Monday,2=Tuesday, etc.)
- table for numbers (0=th, 1=st,2=nd, 3=rd, etc.)

type in the cell c6:
=DAY(DATE($C$4,$B$4,ROW(B6)+9))
&
VLOOKUP(MOD(DAY(DATE($C$4,$B$4,ROW(B6)+9)),10),$G$2:$H$11,2)
&" "&
VLOOKUP(WEEKDAY(DATE($C$4,$B$4,ROW(B6)+9),2),$E$2:$F$8,2)

+9 is because the formula is in C6 ->Row#6 so add 9 to get 15

i put the weekday table on $E$2:$F$8
and numbers table on $G$2:$H$11

you can drag the formula down until the 14th date. But the only problem is
just it can't stop automatically at 14th date. you'll have to drag them
manually. But a little VBA surely can do that.

 

[Patrick Molloy]

In C4 put the month number ( 1 through 12 )
in C5 put the year, eg 2005

add the following code:


Sub FillDates()

Range("C5") = DateSerial(Range("C3"), Range("C2"), 15)

With Range("c5:c35")
.ClearContents
Range("C5") = DateSerial(Range("C3"), Range("C2"), 15)
.DataSeries Rowcol:=xlColumns, Type:=xlChronological, Date:= _
xlDay, Step:=1, stop:=DateSerial(Range("C3"), Range("C2") + 1, 14)
.NumberFormat = "DD MMM, DDD"
End With

End Sub

Method: the range C5:35 is the output area. The code first clears its
contents - not its formatting, so you can color the range if you want to make
it clearer.
Th efirst date, the 15th of the seired month is placed in the first cell of
the output area, C5, and then we use a series fill, using the 14th of the
next month as our stop date.

Using the fill series, we could as easily just get week days
change Date:=xlDay to date:=xlWeekday

HTH
Patrick Molloy

 

[Harald Staff]

Hi Mark

See if this works. My computer's date settings are norwegian, so I don't
know how this runs in your country:

Sub MakeMonth()
Dim S As String
Dim D1 As Date, D As Date
Dim R As Long
Dim i As Long

S = InputBox("Enter month year, like """ & _
Format(Date, "mmm yyyy") & """:")
If S = "" Then Exit Sub

i = 0
Do
i = i + 1
If i >= Len(S) Then Exit Sub
Loop Until Val(Mid$(S, i)) > 0
S = Left(S, i) & " 15 " & Mid(S, i)
If Not IsDate(S) Then Exit Sub

D1 = DateValue(S)
R = 6
For D = D1 To DateSerial(Year(D1), Month(D1) + 1, 14)
Cells(R, 1).Value = Format(D, "mmm d dddd")
R = R + 1
Next

End Sub



HTH. Best wishes Harald