Working with dates & VBA

[mtterry]

I have a data set with a column that contains, among other things, dates sorted from oldest to newest. Part of what I’m looking to do is write a sub that goes through the dates and deletes weekends along with other specific holidays. Additionally, if there is a date missing that is not a weekend and not one of the specific holidays, the sub inserts a row with that missing date and leaves the remaining data in that row blank. The start and end dates are routinely changing. Any help would be greatly appreciated.

 

[JoeMo]

You could improve your chances of getting some help by providing some more details (like what column are the dates in and starting in what row, what specific holidays, ...) and some sample data that can be copied and pasted into Excel.

 

[mtterry]

The dates are in column A, starting in row 2. I can't provide all the specific holidays - they are a bit unique and some aren't technically holidays, I just used that as an example. I have a list of all these dates. Below is a sample - the table on the left is an example of how the data may come in, and the table on the right is how I would like it to look. The change: 3/3/2018 was removed (a weekend), 3/9/2018 was also removed (say it was on my list of dates to omit), then two rows were inserted to the table and dates 3/6/2018 and 3/7/2018 were added to column A (neither weekends nor dates on my list to omit) - the appropriates dates were added, but Field 1 was left blank. Hopefully this helps illustrate, let me know if not.

[TABLE="width: 465"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Field 1[/TD]
[TD][/TD]
[TD]Date[/TD]
[TD]Field 1[/TD]
[/TR]
[TR]
[TD="align: right"]3/2/2018[/TD]
[TD]abc[/TD]
[TD][/TD]
[TD="align: right"]3/2/2018[/TD]
[TD]abc[/TD]
[/TR]
[TR]
[TD="align: right"]3/2/2018[/TD]
[TD]def[/TD]
[TD][/TD]
[TD="align: right"]3/2/2018[/TD]
[TD]def[/TD]
[/TR]
[TR]
[TD="align: right"]3/2/2018[/TD]
[TD]ghi[/TD]
[TD][/TD]
[TD="align: right"]3/2/2018[/TD]
[TD]ghi[/TD]
[/TR]
[TR]
[TD="align: right"]3/2/2018[/TD]
[TD]jkl[/TD]
[TD][/TD]
[TD="align: right"]3/2/2018[/TD]
[TD]jkl[/TD]
[/TR]
[TR]
[TD="align: right"]3/3/2018[/TD]
[TD]abc[/TD]
[TD][/TD]
[TD="align: right"]3/5/2018[/TD]
[TD]abc[/TD]
[/TR]
[TR]
[TD="align: right"]3/5/2018[/TD]
[TD]abc[/TD]
[TD][/TD]
[TD="align: right"]3/5/2018[/TD]
[TD]def[/TD]
[/TR]
[TR]
[TD="align: right"]3/5/2018[/TD]
[TD]def[/TD]
[TD][/TD]
[TD="align: right"]3/5/2018[/TD]
[TD]ghi[/TD]
[/TR]
[TR]
[TD="align: right"]3/5/2018[/TD]
[TD]ghi[/TD]
[TD][/TD]
[TD="align: right"]3/5/2018[/TD]
[TD]jkl[/TD]
[/TR]
[TR]
[TD="align: right"]3/5/2018[/TD]
[TD]jkl[/TD]
[TD][/TD]
[TD="align: right"]3/6/2018[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]3/8/2018[/TD]
[TD]abc[/TD]
[TD][/TD]
[TD="align: right"]3/7/2018[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]3/8/2018[/TD]
[TD]def[/TD]
[TD][/TD]
[TD="align: right"]3/8/2018[/TD]
[TD]abc[/TD]
[/TR]
[TR]
[TD="align: right"]3/8/2018[/TD]
[TD]ghi[/TD]
[TD][/TD]
[TD="align: right"]3/8/2018[/TD]
[TD]def[/TD]
[/TR]
[TR]
[TD="align: right"]3/8/2018[/TD]
[TD]jkl[/TD]
[TD][/TD]
[TD="align: right"]3/8/2018[/TD]
[TD]ghi[/TD]
[/TR]
[TR]
[TD="align: right"]3/9/2018[/TD]
[TD]abc[/TD]
[TD][/TD]
[TD="align: right"]3/8/2018[/TD]
[TD]jkl[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 465"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[JoeMo]

Thanks for providing more detail. The code below is lightly tested, but works well on your posted data. Assumes the Date header is in A1 and the dates are sorted as in your posted data. I placed Holidays in a vertical range that I named "Holidays". In the code that named range is transposed to an array called Xcept in order to eliminate the holidays.

Row deletion is an inherently slow process. If you have a long list of dates this may be noticeable. If so, there are ways to speed things up.

Sub WorkWithDates()
Dim R As Range, lR As Long, i As Long, D As Long, j As Long, Xcept As Variant
lR = Range("A" & Rows.Count).End(xlUp).Row
Set R = Range("A1:A" & lR)
Application.ScreenUpdating = False
'List all inclusive dates
For i = lR To 3 Step -1
    D = R(i) - R(i - 1)
    If D > 1 Then
        With R(i)
            .Resize(D - 1, 1).EntireRow.Insert
            For j = 0 To D - 2
                R(i).Offset(j, 0).Value = R(i + D - 1).Value - (D - 1) + j
            Next j
        End With
    End If
Next i
'Delete wkends and holidays
Xcept = Application.Transpose(Range("Holidays"))
lR = Range("A" & Rows.Count).End(xlUp).Row
Set R = Range("A1:A" & lR)
For i = lR To 2 Step -1
    If Weekday(R(i)) = 1 Or Weekday(R(i)) = 7 Then
        R(i).EntireRow.Delete
    Else
        For j = LBound(Xcept) To UBound(Xcept)
            If R(i) = Xcept(j) Then
                R(i).EntireRow.Delete
                Exit For
            End If
        Next j
    End If
Next i
Application.ScreenUpdating = True
End Sub

 

[mtterry]

Thanks for the reply - I'm out of the office but will test this shortly. I appreciate the assistance.

 

[mtterry]

Ok so looks like I could maintain a separate spreadsheet with the dates to exclude as well and reference that - or input all the dates manually as well if I wanted into the actual sub too?

 

[JoeMo]

Ok so looks like I could maintain a separate spreadsheet with the dates to exclude as well and reference that - or input all the dates manually as well if I wanted into the actual sub too?

Whatever works for you. You don't have to put them on a separate sheet, but it can be done that way. If you name the range with the "Holidays" with a workbook-level scope it can go anywhere in the workbook and the code will not have to be altered. Just remember to adjust the range if you add or remove dates after naming it.

 

[mtterry]

Ok great, thanks so much for your help!

 

[JoeMo]

Ok great, thanks so much for your help!

You are welcome - thanks for the reply.