adding missing dates

T

Tody03

New Member
Joined
Jun 21, 2014
Messages
48
I have a table with several columns. The first is a name and the second is a date. For each name there are several dates. The dates should be sequential. If there is a gap of one or more days, I would like to add a line with the name and the missing date.
[TABLE="width: 128"]
<tbody>[TR]
[TD]david[/TD]
[TD]3/5/19[/TD]
[/TR]
[TR]
[TD]david[/TD]
[TD]3/6/19[/TD]
[/TR]
[TR]
[TD]david[/TD]
[TD]3/8/19[/TD]
[/TR]
[TR]
[TD]david[/TD]
[TD]3/9/19[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]3/11/19[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]3/15/19[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]3/16/19[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
You could do it with Power Query, but here is a VBA solution

Code: 
Sub AddDates()
Dim firstdate As Date, lastdate As Date
Dim numrows As Long, numdates As Long, numinsert As Long
Dim lastrow As Long
Dim i As Long
    Application.ScreenUpdating = False
    With ActiveSheet
    
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = lastrow To 2 Step -1
        
            lastdate = .Cells(i, "B").Value
            numrows = Application.CountIf(.Columns(1), .Cells(i, "A").Value)
            firstdate = .Cells(i - numrows + 1, "B").Value
            numdates = lastdate - firstdate + 1
            numinsert = numdates - numrows
            If numinsert > 0 Then .Rows(i + 1).Resize(numinsert).Insert
            .Cells(i - numrows + 1, "A").Resize(numdates).Value = .Cells(i, "A").Value
            .Cells(i - numrows + 1, "B").Resize(numdates).DataSeries Rowcol:=xlColumns, _
                                                                     Type:=xlChronological, _
                                                                     Date:=xlDay, _
                                                                     Step:=1, Stop:=lastdate, _
                                                                     Trend:=False
            i = i - numrows + 1
        Next i
    End With
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Try this
- code inserts a new sheet which is a copy of the original sheet and the macro works on that data
- which makes it easier to rerun whilst testing
- source data remains undamaged!
- amend sheet name in the code to match the name of the sheet containing your data


Source data Starts in A2
Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A​
[/th][th]
B​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1​
[/td][td]Name[/td][td]Date[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2​
[/td][td]david[/td][td]
03/05/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td]david[/td][td]
03/06/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4​
[/td][td]david[/td][td]
03/08/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5​
[/td][td]david[/td][td]
03/09/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6​
[/td][td]John[/td][td]
03/11/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7​
[/td][td]John[/td][td]
03/15/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8​
[/td][td]John[/td][td]
03/16/2019​
[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

New sheet created which looks like this
Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A​
[/th][th]
B​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1​
[/td][td]Name[/td][td]Date[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2​
[/td][td]david[/td][td]
03/05/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td]david[/td][td]
03/06/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4​
[/td][td]david[/td][td]
03/07/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5​
[/td][td]david[/td][td]
03/08/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6​
[/td][td]david[/td][td]
03/09/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7​
[/td][td]John[/td][td]
03/11/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8​
[/td][td]John[/td][td]
03/12/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9​
[/td][td]John[/td][td]
03/13/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
10​
[/td][td]John[/td][td]
03/14/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
11​
[/td][td]John[/td][td]
03/15/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
12​
[/td][td]John[/td][td]
03/16/2019​
[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1 (2)[/td][/tr][/table]

Code: 
Sub InsertDates()
    Dim ws As Worksheet, r As Long, lastRow As Long, diff As Integer
    Dim A1 As Range, A2 As Range, B1 As Range, B2 As Range
    
    Sheets("[COLOR=#ff0000]Sheet1[/COLOR]").Copy After:=Worksheets(Sheets.Count)
    Set ws = Worksheets(Sheets.Count)
    lastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
    
    For r = lastRow - 1 To 2 Step -1
        Set A1 = ws.Cells(r, "A")
        Set A2 = A1.Offset(1)
        Set B1 = A1.Offset(, 1)
        Set B2 = B1.Offset(1)
        diff = B2 - B1
        
        If A1 = A2 And diff > 1 Then
            A2.Resize(diff - 1).EntireRow.Insert
            A1.Resize(diff) = A1
            B1.AutoFill Destination:=B1.Resize(diff), Type:=xlFillDefault
        End If
    Next r
End Sub


I have a table
Click to expand...
- is this a structured table created with Insert Table (the code also works)

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A​
[/th][th]
B​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1​
[/td][td=bgcolor:#4472C4]Name[/td][td=bgcolor:#4472C4]Date[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2​
[/td][td=bgcolor:#D9E1F2]david[/td][td=bgcolor:#D9E1F2]
03/05/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td]david[/td][td]
03/06/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4​
[/td][td=bgcolor:#D9E1F2]david[/td][td=bgcolor:#D9E1F2]
03/08/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5​
[/td][td]david[/td][td]
03/09/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6​
[/td][td=bgcolor:#D9E1F2]John[/td][td=bgcolor:#D9E1F2]
03/11/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7​
[/td][td]John[/td][td]
03/15/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8​
[/td][td=bgcolor:#D9E1F2]John[/td][td=bgcolor:#D9E1F2]
03/16/2019​
[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A​
[/th][th]
B​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1​
[/td][td=bgcolor:#4472C4]Name[/td][td=bgcolor:#4472C4]Date[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2​
[/td][td=bgcolor:#D9E1F2]david[/td][td=bgcolor:#D9E1F2]
03/05/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td]david[/td][td]
03/06/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4​
[/td][td=bgcolor:#D9E1F2]david[/td][td=bgcolor:#D9E1F2]
03/07/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5​
[/td][td]david[/td][td]
03/08/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6​
[/td][td=bgcolor:#D9E1F2]david[/td][td=bgcolor:#D9E1F2]
03/09/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7​
[/td][td]John[/td][td]
03/11/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8​
[/td][td=bgcolor:#D9E1F2]John[/td][td=bgcolor:#D9E1F2]
03/12/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9​
[/td][td]John[/td][td]
03/13/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
10​
[/td][td=bgcolor:#D9E1F2]John[/td][td=bgcolor:#D9E1F2]
03/14/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
11​
[/td][td]John[/td][td]
03/15/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
12​
[/td][td=bgcolor:#D9E1F2]John[/td][td=bgcolor:#D9E1F2]
03/16/2019​
[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1 (4)[/td][/tr][/table]
 
Upvote 0
[TABLE="width: 500"]
<tbody>[TR]
[TD]Thanks for the quick response. It is working. Thanks again[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
theBardd said:
You could do it with Power Query, but here is a VBA solution

Code: 
Sub AddDates()
Dim firstdate As Date, lastdate As Date
Dim numrows As Long, numdates As Long, numinsert As Long
Dim lastrow As Long
Dim i As Long
    Application.ScreenUpdating = False
    With ActiveSheet
    
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = lastrow To 2 Step -1
        
            lastdate = .Cells(i, "B").Value
            numrows = Application.CountIf(.Columns(1), .Cells(i, "A").Value)
            firstdate = .Cells(i - numrows + 1, "B").Value
            numdates = lastdate - firstdate + 1
            numinsert = numdates - numrows
            If numinsert > 0 Then .Rows(i + 1).Resize(numinsert).Insert
            .Cells(i - numrows + 1, "A").Resize(numdates).Value = .Cells(i, "A").Value
            .Cells(i - numrows + 1, "B").Resize(numdates).DataSeries Rowcol:=xlColumns, _
                                                                     Type:=xlChronological, _
                                                                     Date:=xlDay, _
                                                                     Step:=1, Stop:=lastdate, _
                                                                     Trend:=False
            i = i - numrows + 1
        Next i
    End With
    Application.ScreenUpdating = True
End Sub
Click to expand...
 
Upvote 0
Thanks for the quick response. I tested it and it is working. Thanks again, you saved me a lot of time!!!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Auto update DATE but only if cell is empty
Replies
8
Views
211
Candyman8019
C
D
  • Question Question
Locate first instance of all dates in a column and hide all remaining rows
Replies
4
Views
82
Peter_SSs
Peter_SSs
V
  • Solved
Compare data in three sets of columns, highlight non matches
Replies
9
Views
141
Eric W
Eric W
G
  • Locked
  • Question Question
VBA Userform - Change from 1900 Date Format to Long Date
Replies
1
Views
58
Peter_SSs
Peter_SSs
G
  • Question Question
VBA Userform Date Format
Replies
7
Views
182
gilly01625
G

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top