Insert Missing Dates & Other Date query

KyleG

Well-known Member
Joined
Jun 12, 2004
Messages
623
Two Queries:
I have a column with dates like this 2/18/2005 but i want the actual cell text to be 18/2/2005, not just what is displayed.

My main query is....
I have a column(A) of dates (see below) that i want to insert rows with the missing dates so that i have all the dates in the sequence even though there will be multiple rows of some dates i need to have a complete date list.
18-Feb-05
18-Feb-05
23-Feb-05
23-Feb-05
23-Feb-05
23-Feb-05
25-Feb-05
25-Feb-05
25-Feb-05
1-Mar-05
2-Mar-05
2-Mar-05
2-Mar-05
2-Mar-05
4-Mar-05
4-Mar-05
4-Mar-05


Thanks
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

rflyr98

New Member
Joined
Sep 24, 2006
Messages
6
See if this works for you

Sub Macro1()

Dim thisdate As Date
Dim nextdate As Date

thisdate = ActiveCell.Value
ActiveCell.Offset(1, 0).Activate
thatdate = ActiveCell.Value
ActiveCell.Offset(-1, 0).Activate

If (thatdate - thisdate) > 1 Then
ActiveCell.Offset(1, 0).Activate

ActiveCell.Insert

ActiveCell.Value = thisdate + 1
ActiveCell.Offset(-1, 0).Activate

End If

ActiveCell.Offset(1, 0).Activate

End Sub

You could make a loop to run this "x" amount of times
 

rflyr98

New Member
Joined
Sep 24, 2006
Messages
6
Oh also I started assuming you had the top cell active and then running the macro, it runs, then steps down.
 

KyleG

Well-known Member
Joined
Jun 12, 2004
Messages
623
Ok this works but errors at end so its ok.

Sub Macro1()
Dim thisdate As Date
Dim nextdate As Date
Do Until thisdate = 9 / 22 / 6
thisdate = ActiveCell.Value
ActiveCell.Offset(1, 0).Activate
thatdate = ActiveCell.Value
ActiveCell.Offset(-1, 0).Activate
If (thatdate - thisdate) > 1 Then
ActiveCell.Offset(1, 0).Activate
ActiveCell.EntireRow.Insert Shift:=xlDown
ActiveCell.Select
ActiveCell.Value = thisdate + 1
ActiveCell.Offset(-1, 0).Activate
End If
ActiveCell.Offset(1, 0).Activate
Loop
End Sub

So now a new question has formed...
I have done this to two sheets of data...I need to make both sheets have the same number of each date.
So if sheet 1 has 3
23-Feb-05
23-Feb-05
23-Feb-05
I need Sheet 2 to also have 3
and if it is sheet 2 with the greater number then i need sheet 1 to increase the number of rows
So that in the end i am left with the same number of rows in each sheet.
 

Forum statistics

Threads
1,136,419
Messages
5,675,724
Members
419,584
Latest member
mrh8rr

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
Top