Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning each month between 2 dates
Hi,
I have a "start date" in $B$12 and an "end date" in $C$12. Starting in $B$16, I need to have each month between these two dates populated. It would be nice if there were duplicates of each month in this column. For example, if $B$12 was April 1, 2006 and $C$12 was March 31, 2007, a procedure would place "April-06" in $B$16 and $B$17. "May-06" would be placed in $B$18 and $B$19, "June-06" would be placed in $B$20 and $B$21, and so on until the last month of "March-07" would be placed in the final two cells in column B. I have 53 rows formatted to accept a list of months, so I would like the remainding rows after the last entered month ("March-07" in the example), to be hidden. Is this an easy procedure to code? As usual, I would be very appreciable if someone would be able to help me on this problem. Thanks in advance! Kent. -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.943.9098 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning each month between 2 dates
Kent,
For a worksheet function, try this. It is an array formula, so select the output cell, enter the function and arguments as required and press SHIFT+CONTROL+ENTER, not just ENTER. If you do not want to duplicate the month list, set the Duplicate argument to False. You should add checks for valid dates and error trapping: Public Function GetMonths(StartDate As Date, EndDate As Date, Optional Duplicate As Boolean = True) As Variant Dim MonthCount As Long Dim TempDates() As Date Dim i As Long Dim NewDate As Date MonthCount = DateDiff("m", StartDate, EndDate) If Duplicate = True Then ReDim TempDates(0 To MonthCount * 2 + 1) Else ReDim TempDates(0 To MonthCount) End If For i = 0 To MonthCount NewDate = DateAdd("m", i, StartDate) If Duplicate = True Then TempDates(i * 2) = NewDate TempDates(i * 2 + 1) = NewDate Else TempDates(i) = NewDate End If Next If Application.Caller.Columns.Count 1 Then GetMonths = TempDates Else GetMonths = Application.WorksheetFunction.Transpose(TempDates) End If End Function NickHK "klysell" .(donotspam) wrote in message ... Hi, I have a "start date" in $B$12 and an "end date" in $C$12. Starting in $B$16, I need to have each month between these two dates populated. It would be nice if there were duplicates of each month in this column. For example, if $B$12 was April 1, 2006 and $C$12 was March 31, 2007, a procedure would place "April-06" in $B$16 and $B$17. "May-06" would be placed in $B$18 and $B$19, "June-06" would be placed in $B$20 and $B$21, and so on until the last month of "March-07" would be placed in the final two cells in column B. I have 53 rows formatted to accept a list of months, so I would like the remainding rows after the last entered month ("March-07" in the example), to be hidden. Is this an easy procedure to code? As usual, I would be very appreciable if someone would be able to help me on this problem. Thanks in advance! Kent. -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.943.9098 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning each month between 2 dates
Hi
Into cell B16 enter the formula =IF(DATE(YEAR($B$12),MONTH($B$12)+INT((ROW()-16)/2),1)DATE(YEAR($C$12),MONTH($C$12),1),"",DATE(YEA R($B$12),MONTH($B$12)+INT((ROW()-16)/2),1)) , and format as Custom "mmm-yy" or "mmmm-yy". Copy B16 down for as much as you planned. -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "klysell" .(donotspam) wrote in message ... Hi, I have a "start date" in $B$12 and an "end date" in $C$12. Starting in $B$16, I need to have each month between these two dates populated. It would be nice if there were duplicates of each month in this column. For example, if $B$12 was April 1, 2006 and $C$12 was March 31, 2007, a procedure would place "April-06" in $B$16 and $B$17. "May-06" would be placed in $B$18 and $B$19, "June-06" would be placed in $B$20 and $B$21, and so on until the last month of "March-07" would be placed in the final two cells in column B. I have 53 rows formatted to accept a list of months, so I would like the remainding rows after the last entered month ("March-07" in the example), to be hidden. Is this an easy procedure to code? As usual, I would be very appreciable if someone would be able to help me on this problem. Thanks in advance! Kent. -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.943.9098 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning each month between 2 dates
Hi Kent -
See comment in code below for labeling options. Sub klysellDoubleMonth() With Range("B16:B68") .NumberFormat = "@" .HorizontalAlignment = xlCenter End With startDate = Range("B12").Value endDate = Range("C12").Value nextDate = startDate: i = 0 Do nextDate = DateSerial(Year(startDate), Month(startDate) + i, Day(startDate)) 'Next statement: change True to False for non-abbreviated month name dateLabel = MonthName(Month(nextDate), True) & _ "-" & Right(Year(nextDate), 2) Range("B16:B17").Offset(i * 2, 0) = dateLabel i = i + 1 Loop Until Month(nextDate) = Month(endDate) And _ Year(nextDate) = Year(endDate) Range(Range("B16").End(xlDown)(2, 1), _ Range("B68")).EntireRow.Hidden = True End Sub -- Jay "klysell" wrote: Hi, I have a "start date" in $B$12 and an "end date" in $C$12. Starting in $B$16, I need to have each month between these two dates populated. It would be nice if there were duplicates of each month in this column. For example, if $B$12 was April 1, 2006 and $C$12 was March 31, 2007, a procedure would place "April-06" in $B$16 and $B$17. "May-06" would be placed in $B$18 and $B$19, "June-06" would be placed in $B$20 and $B$21, and so on until the last month of "March-07" would be placed in the final two cells in column B. I have 53 rows formatted to accept a list of months, so I would like the remainding rows after the last entered month ("March-07" in the example), to be hidden. Is this an easy procedure to code? As usual, I would be very appreciable if someone would be able to help me on this problem. Thanks in advance! Kent. -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.943.9098 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning each month between 2 dates
Wow! You guys are the Excel gods of the planet. Thanks so much!
-- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.943.9098 "Jay" wrote: Hi Kent - See comment in code below for labeling options. Sub klysellDoubleMonth() With Range("B16:B68") .NumberFormat = "@" .HorizontalAlignment = xlCenter End With startDate = Range("B12").Value endDate = Range("C12").Value nextDate = startDate: i = 0 Do nextDate = DateSerial(Year(startDate), Month(startDate) + i, Day(startDate)) 'Next statement: change True to False for non-abbreviated month name dateLabel = MonthName(Month(nextDate), True) & _ "-" & Right(Year(nextDate), 2) Range("B16:B17").Offset(i * 2, 0) = dateLabel i = i + 1 Loop Until Month(nextDate) = Month(endDate) And _ Year(nextDate) = Year(endDate) Range(Range("B16").End(xlDown)(2, 1), _ Range("B68")).EntireRow.Hidden = True End Sub -- Jay "klysell" wrote: Hi, I have a "start date" in $B$12 and an "end date" in $C$12. Starting in $B$16, I need to have each month between these two dates populated. It would be nice if there were duplicates of each month in this column. For example, if $B$12 was April 1, 2006 and $C$12 was March 31, 2007, a procedure would place "April-06" in $B$16 and $B$17. "May-06" would be placed in $B$18 and $B$19, "June-06" would be placed in $B$20 and $B$21, and so on until the last month of "March-07" would be placed in the final two cells in column B. I have 53 rows formatted to accept a list of months, so I would like the remainding rows after the last entered month ("March-07" in the example), to be hidden. Is this an easy procedure to code? As usual, I would be very appreciable if someone would be able to help me on this problem. Thanks in advance! Kent. -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.943.9098 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Returning a zero if there is no month given | Excel Discussion (Misc queries) | |||
returning value if month is January | Excel Worksheet Functions | |||
Dates - Several Days In a month to month only | Excel Discussion (Misc queries) | |||
Fill column with dates of month depending on month in A1 | Excel Programming | |||
Workaround for returning month name? | Excel Worksheet Functions |