fill series using from-to date range? (October 4-17, 2020, etc.)

[chraco]

Hi, I can't find anything about filling a series of dates, based on a format such as October 4-17, 2020, October 18-31, 2020, etc. Clearly, the auto-fill utility is no help, so I'm guessing a formula is in order. Any takers?

Thanks!

 

[alansidman]

Sub AutoDateFill()
    Dim x As Date
    Dim rn As Long
    Dim cn As String, sn As Long
    cn = InputBox("Which Column to fill?")
    
    
    rn = InputBox("How many rows to fill?")
    sn = InputBox("Which row to start fill?")

    Application.ScreenUpdating = False
    Application.StatusBar = "Macro Running"
    x = InputBox("What is the starting Date? mm/dd/yyyy")
    Range(cn & sn).Select
    Do Until ActiveCell.Row = rn + sn
        If ActiveCell.EntireRow.Hidden = False Then
            ActiveCell.Value = x
            x = x + 1
        End If
        ActiveCell.Offset(1).Select
    Loop
    Application.ScreenUpdating = True
    Application.StatusBar = "Completed"
End Sub

 

[chraco]

Thanks alansidman! However, the code just filled a sequential series of single dates, exactly as if I'd grabbed the auto-fill handle and dragged down.

That's all right, I just typed my list in manually. There were only about 30 cells to fill and this issue only comes up a few times per year. Although it's an interesting problem, it's probably not worth solving. The effort to benefit ratio seems too high.

Thanks anyway!

 

[Darren Bartrup]

It looks like you're trying to do this:

Book2
	B	C	D
5	04/10/2020		October 4-17, 2020
6	18/10/2020		October 18-31, 2020
7	01/11/2020		November 1-14, 2020
8	15/11/2020		November 15-28, 2020
9	29/11/2020		November 29-12, 2020
10	13/12/2020		December 13-26, 2020
11	27/12/2020		December 27-9, 2021
12	10/01/2021		January 10-23, 2021
13	24/01/2021		January 24-6, 2021
14	07/02/2021		February 7-20, 2021
15	21/02/2021		February 21-6, 2021
16	07/03/2021		March 7-20, 2021
Sheet1

Cell Formulas
Range	Formula
B6:B16	B6	=SUM(B5,14)
D5:D16	D5	=TEXT(B5,"mmmm d-") & TEXT(B5+13,"d, yyyy")

But what happens when the end date is in another month? 29th Nov - 12th Dec for instance.

 

[chraco]

Thanks Darren, I appreciate your post and your approach is clever. But as you noted, there's still a problem to solve when a cell contains a range spanning two months.

If I had to do this every day, or on a project that required 1,000 cells to be filled, it might be worth pursuing. But I suspect the solution to this problem requires too much code to wrangle for my occasional and relatively simple needs.

Thanks for your time everyone, I appreciate it.