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

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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
169
Candyman8019
C
J
  • Question Question
Calculating time frames
Replies
2
Views
317
myall_blues
myall_blues
S
  • Solved
Networkdays formula issue
Replies
3
Views
258
Joe4
Joe4
Lacan
  • Solved
Convert Months Spanish Language to Portuguese and do Lookups + Sum?
Replies
2
Views
185
Lacan
Lacan
S
  • Solved
Comparing time stamp values to remove 'duplicate' time entries (rows).
Replies
7
Views
293
felixstraube
F

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

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