none
Auto fill Dates Macro that skips weekends RRS feed

  • Question

  • I want a macro that auto fills dates from the current to an amount of days in the future that skips weekends. If it cannot be done with an macro an formula would work.

    for example if the first date in the table is 5/21/2013 and I need the next 10 days filled in I would need

    Monday 5/21, 5/22 , 5/23, , 5/24 , 5/25, Monday  5/27, 5/28/, 5/29, 5/30

    I would also want it to remain true if I started on a wednesday instead of an monday

    Any Ideas?


    Sunday, May 19, 2013 4:45 PM

Answers

  • On Sun, 19 May 2013 16:45:43 +0000, Ruda Windows Fan wrote:
     
    >
    >
    >I want a macro that auto fills dates from the current to an amount of days in the future that skips weekends. If it cannot be done with an macro an formula would work.
    >
    >for example if the first date in the table is 5/21/2013 and I need the next 10 days filled in I would need
    >
    >Monday 5/21, 5/22 , 5/23, , 5/24 , 5/25, Monday  5/27, 5/28/, 5/29, 5/30
    >
    >
    >
    >I would also want it to remain true if I started on a wednesday instead of an monday
    >
    >Any Ideas?
    >
    >
     
    This can be done easily with either a macro or worksheet functions.
     
    Assume
      A1:  First Date
      B1:  Number of days to fill in after first date
     
    Formula:
      A2:  =IFERROR(WORKDAY($A$1,ROWS($1:1)/(ROWS($1:1)<=$B$1)),"")
    Fill down at least as far as the maximum possible number of days.
     
    Macro:
    To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
    Ensure your project is highlighted in the Project Explorer window.
    Then, from the top menu, select Insert/Module and
    paste the code below into the window that opens.
     
    The macro assumes your information is as above, and that you want to fill in column C, but you can easily change that in the obvious places in the macro
     
    To use this Macro (Sub), <alt-F8> opens the macro dialog box. Select the macro by name, and <RUN>.
     
    ===============================================
    Option Explicit
    Sub FillBusDates()
        Dim dFirst As Date
        Dim lNumDays As Long
        Dim v() As Date
        Dim i As Long
        Dim rDest As Range
    Set rDest = Range("C1")
    dFirst = Range("a1").Value
    lNumDays = Range("b1").Value
     
    ReDim v(0 To lNumDays)
    v(0) = dFirst
    For i = 1 To lNumDays
        v(i) = WorksheetFunction.WorkDay(dFirst, i)
    Next i
     
    rDest.EntireColumn.ClearContents
    rDest.Resize(UBound(v) + 1) = WorksheetFunction.Transpose(v)
    End Sub
    ====================================
     

    Ron
    Sunday, May 19, 2013 6:25 PM

All replies

  • On Sun, 19 May 2013 16:45:43 +0000, Ruda Windows Fan wrote:
     
    >
    >
    >I want a macro that auto fills dates from the current to an amount of days in the future that skips weekends. If it cannot be done with an macro an formula would work.
    >
    >for example if the first date in the table is 5/21/2013 and I need the next 10 days filled in I would need
    >
    >Monday 5/21, 5/22 , 5/23, , 5/24 , 5/25, Monday  5/27, 5/28/, 5/29, 5/30
    >
    >
    >
    >I would also want it to remain true if I started on a wednesday instead of an monday
    >
    >Any Ideas?
    >
    >
     
    This can be done easily with either a macro or worksheet functions.
     
    Assume
      A1:  First Date
      B1:  Number of days to fill in after first date
     
    Formula:
      A2:  =IFERROR(WORKDAY($A$1,ROWS($1:1)/(ROWS($1:1)<=$B$1)),"")
    Fill down at least as far as the maximum possible number of days.
     
    Macro:
    To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
    Ensure your project is highlighted in the Project Explorer window.
    Then, from the top menu, select Insert/Module and
    paste the code below into the window that opens.
     
    The macro assumes your information is as above, and that you want to fill in column C, but you can easily change that in the obvious places in the macro
     
    To use this Macro (Sub), <alt-F8> opens the macro dialog box. Select the macro by name, and <RUN>.
     
    ===============================================
    Option Explicit
    Sub FillBusDates()
        Dim dFirst As Date
        Dim lNumDays As Long
        Dim v() As Date
        Dim i As Long
        Dim rDest As Range
    Set rDest = Range("C1")
    dFirst = Range("a1").Value
    lNumDays = Range("b1").Value
     
    ReDim v(0 To lNumDays)
    v(0) = dFirst
    For i = 1 To lNumDays
        v(i) = WorksheetFunction.WorkDay(dFirst, i)
    Next i
     
    rDest.EntireColumn.ClearContents
    rDest.Resize(UBound(v) + 1) = WorksheetFunction.Transpose(v)
    End Sub
    ====================================
     

    Ron
    Sunday, May 19, 2013 6:25 PM
  • Now what if I wanted to add blank cells between each of the dates.

    Actually now Windows 7 Professional 64 bit user

    Sunday, May 19, 2013 6:39 PM
  • using the formula or macro approach?

    Ron

    Sunday, May 19, 2013 7:33 PM
  • Macro


    Actually now Windows 7 Professional 64 bit user

    Sunday, May 19, 2013 7:35 PM
  • Option Explicit Sub FillBusDates() Dim dFirst As Date Dim lNumDays As Long Dim v() As Variant Dim i As Long Dim rDest As Range Set rDest = Range("a1") dFirst = Range("a1").Value lNumDays = Range("b1").Value ReDim v(0 To lNumDays * 2) v(0) = dFirst For i = 1 To lNumDays * 2 Step 2 v(i) = "" v(i + 1) = WorksheetFunction.WorkDay(dFirst, (i + 1) / 2) Next i rDest.EntireColumn.ClearContents rDest.Resize(UBound(v) + 1) = WorksheetFunction.Transpose(v) End Sub

    The above macro will overwrite the original in column A. Again you can easily change that.

    Ron


    Sunday, May 19, 2013 7:40 PM
  • sorry to be repetative

    Which variable do I change to make more spaces in between?


    Actually now Windows 7 Professional 64 bit user

    Sunday, May 19, 2013 7:55 PM
  • You have to change some variables and also change the code if you are going to skip more than one row. 

    Try this.  The variables to be changed are at the beginning.  The number of days to add after the first date is now hard coded instead of on the worksheet; as is the number of blank rows to leave between each date.

    Option Explicit
    Sub FillBusDates()
        Const lNumDays As Long = 5
        Const lRowsToSkip As Long = 3
        Dim dFirst As Date
        Dim v() As Variant
        Dim i As Long, j As Long
        Dim rDest As Range
    dFirst = Range("a1").Value
    Set rDest = Range("c1")
    
    ReDim v(0 To lNumDays * (lRowsToSkip + 1))
    v(0) = dFirst
    For i = 1 To lNumDays * (lRowsToSkip + 1) Step lRowsToSkip + 1
        For j = 0 To lRowsToSkip - 1
            v(i + j) = ""
        Next j
        v(i + j) = CDate(WorksheetFunction.WorkDay(dFirst, (i + j) / (lRowsToSkip + 1)))
    Next i
    
    rDest.EntireColumn.ClearContents
    rDest.Resize(UBound(v) + 1) = WorksheetFunction.Transpose(v)
    End Sub


    Ron


    Sunday, May 19, 2013 8:22 PM