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
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

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
 

Forum statistics

Threads
1,144,390
Messages
5,724,074
Members
422,535
Latest member
navjeet

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