Problem with Date....

ru_1985

New Member
Joined
Oct 4, 2006
Messages
35
--------------------------------------------------------------------------------

Given a list of dates. Would it be able to check the list of dates, sort it in ascending order and check whether there is any missing date, if there is missing date can it be able to insert the date rather than a blank row. The example is shown below..

EXAMPLE

BEFORE
row 1 13/6/2005 0.1
row 2 14/6/2005 0.5
row 3 16/6/2005 0.12
row 4 17/6/2005 0.31

AFTER
row 1 13/6/2005 0.1
row 2 14/6/2005 0.5
row 3 15/6/2005
row 4 16/6/2005 0.12
row 5 17/6/2005 0.31


Code:
Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Range("A2:C" & Cells(Rows.Count, 1).End(xlUp).Row).Sort key1:=Range("B2"), order1:=xlAscending, header:=xlNo
    Columns("B:B").TextToColumns Destination:=Range("E1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1))
    Range("E3").Select
    While Not IsEmpty(ActiveCell)
    If ActiveCell <> ActiveCell.Offset(-1, 0) + 1 And ActiveCell <> ActiveCell.Offset(-1, 0) Then
      t = ActiveCell - ActiveCell.Offset(-1, 0)
      ActiveCell.Resize(t - 1).EntireRow.Insert
      ActiveCell.Offset(t, 0).Select
    Else
      ActiveCell.Offset(1, 0).Select
    End If
    Wend
    Columns("E:G").Delete
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi ru_1985

Your code makes reference to a lot of columns.

I assumed that the data is like in your post. In column A starting in A1 you have dates. In column B you have the corresponding values.

For that configuration, please try:

Code:
Sub SortCompleteDate()
Dim lRow As Long, lRowMax As Long

Application.ScreenUpdating = False
Range("A1", Cells(Rows.Count, "A").End(xlUp)).Sort key1:=Range("A1"), order1:=xlAscending, header:=xlNo
lRowMax = Cells(Rows.Count, 1).End(xlUp).Row
For lRow = lRowMax To 2 Step -1
    Do While Cells(lRow, "A") <> Cells(lRow, "A").Offset(-1) + 1
        Cells(lRow, "A").Resize(, 2).Insert
        Cells(lRow, "A") = Cells(lRow, "A").Offset(1) - 1
    Loop
Next
Application.ScreenUpdating = True
End Sub

Hope this helps
PGC
 

ru_1985

New Member
Joined
Oct 4, 2006
Messages
35
The code is great, but there is a problem in it. wat if my date contain time in it?

Example:
Before
Row 1 2006/09/01 15:15 0.1
Row 2 2006/09/03 13:10 0.4


After
Row 1 2006/09/01 15:15 0.1
Row 2 2006/09/02 23:00
Row 3 2006/09/03 13:10 0.4
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi again

If your date has time we'll work just with the date.

I'm creating the days at 00:00:00 because you did not explain how you found the 23:00 in the inserted line.

Please explain the algorithm for the time in a newly created day.

Code:
Sub SortCompleteDate()
Dim lRow As Long, lRowMax As Long

Application.ScreenUpdating = False
Range("A1", Cells(Rows.Count, "A").End(xlUp)).Sort key1:=Range("A1"), order1:=xlAscending, header:=xlNo
lRowMax = Cells(Rows.Count, 1).End(xlUp).Row
For lRow = lRowMax To 2 Step -1
    Do While Int(Cells(lRow, "A")) <> Int(Cells(lRow, "A").Offset(-1)) + 1
        Cells(lRow, "A").Resize(, 2).Insert
        Cells(lRow, "A") = Int(Cells(lRow, "A").Offset(1)) - 1
    Loop
Next
Application.ScreenUpdating = True
End Sub

Hope this helps
PGC
 

ru_1985

New Member
Joined
Oct 4, 2006
Messages
35
It's a mistake... Don need to set at 23:59... why did the code hang whenever i go it when my source code??? :eek: :eek:
 

Forum statistics

Threads
1,136,613
Messages
5,676,813
Members
419,652
Latest member
jjakub33

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