Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dates and VBA Magic!
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dates and VBA Magic!
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. "tip2tail" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dates and VBA Magic!
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 "tip2tail" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dates and VBA Magic!
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 "tip2tail" skrev i melding ups.com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IDE add-on VB Magic? | Excel Discussion (Misc queries) | |||
My new book is available -- "This isn't Excel, it's Magic" | Excel Discussion (Misc queries) | |||
Magic Cells | Excel Discussion (Misc queries) | |||
Macro Magic Wand | Excel Programming | |||
Magic Shrinking Listbox | Excel Programming |