Problem with date.. PLEASE HELP!!!

ru_1985

New Member
Joined
Oct 4, 2006
Messages
35
Hi all,
Given a list of dates with different times. 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.. ( The date will be repeating)

EXAMPLE

BEFORE
row 1 13/6/2005 12:01 --0.1
row 2 14/6/2005 14:08 --0.5
row 3 16/6/2005 08:65 --0.12
row 4 16/6/2005 11:46 --0.31

AFTER
row 1 13/6/2005 12:01 --0.1
row 2 14/6/2005 14:08 --0.5
row 3 15/6/2005 00:00 --
row 4 16/6/2005 08:65 --0.12
row 5 16/6/2005 11:46 --0.31

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

This code can only work with NO REPEATING date
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
presume the dates in column A are sorted in ascending order.
then try this macro . if there is bug revert to working group

Code:
Sub test()
Dim i, j As Integer
Dim rng, c As Range
Set rng = Range(Range("a2"), Range("a2").End(xlDown))
For Each c In rng
If c - c.Offset(-1, 0) > 1 Then
j = c - c.Offset(-1, 0)
For i = 1 To j - 1
c.EntireRow.Insert
c.Offset(-1, 0) = c.Offset(-2, 0) + 1
c.Offset(-1, 1) = "00:00"
c.offset(-1,2)="---"
Next i
End If
Next c
End Sub

not completely tested
 

ru_1985

New Member
Joined
Oct 4, 2006
Messages
35
Thanks, the code is smiliar to wat i want but there is some error...
Code:
Sub test() 
Dim i, j As Integer 
Dim rng, c As Range 
Set rng = Range(Range("a2"), Range("a2").End(xlDown)) 
For Each c In rng 
If c - c.Offset(-1, 0) > 1 Then 
j = c - c.Offset(-1, 0) 
For i = 1 To j - 1 
c.EntireRow.Insert 
c.Offset(-1, 0) = c.Offset(-2, 0) + 1 

Next i 
End If 
Next c 
End Sub

i had change to wat i want.. but there is some little thing which is not right...

Can the missing date, display in this format? 2006/09/10 00:00
i mean the time in 00:00....


Thank alots....
 

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
see whether this helps
Code:
Sub test()
Dim i, j As Integer
Dim rng, c As Range
Set rng = Range(Range("a2"), Range("a2").End(xlDown))
For Each c In rng
If c - c.Offset(-1, 0) > 1 Then
j = c - c.Offset(-1, 0)
For i = 1 To j - 1
c.EntireRow.Insert
c.Offset(-1, 0) = DateValue(c.Offset(-2, 0)) + 1 & " " & "00:00"
' comment -you take the date value which is the date and give a space and add 0 time
Next i
End If
Next c
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,114,041
Messages
5,545,679
Members
410,697
Latest member
srishtijain0708
Top