Sub Change2Dates()
'changes text string dates in the format "2/12/2006 6.53" to true Excel dates
'process all cells in the selected range
Dim Cell As Range
Dim strDate As String
For Each Cell In Selection
If Not IsEmpty(Cell) Then
strDate = Replace(Cell.Text, ".", ":")
If IsDate(strDate) Then Cell.Value = CDate(strDate)
End If
Next Cell
End Sub
Sub GroupByDate()
'Sorts dates, then looks for date changes in column A and inserts
'blank color-filled row (cols A and B only) when date changes
'changes text string dates in the format "2/12/2006 6.53" to true Excel dates
'process all cells in column A
Dim Cell As Range
Dim strDate As String
For Each Cell In Range("A:A")
If Not IsEmpty(Cell) Then
strDate = Replace(Cell.Text, ".", ":")
If IsDate(strDate) Then Cell.Value = CDate(strDate)
End If
Next Cell
Dim iRow As Long
Dim LastCell As Range
Dim DT As Date
'Sort columns A and B using col A (dates) as the key.
Set LastCell = Range("B65536").End(xlUp)
Range("A2", LastCell).Sort key1:=Range("A1")
'Now insert grouping separators
iRow = 2
Do
If Int(Cells(iRow + 1, "A")) > Int(Cells(iRow, "A")) Then
'date change occurred between iRow and iRow+1
iRow = iRow + 1
Rows(iRow).Insert
Range(Cells(iRow, "A"), Cells(iRow, "B")).Interior.Color = RGB(100, 100, 100)
End If
iRow = iRow + 1
Loop Until IsEmpty(Cells(iRow, "A"))
End Sub
Sub GroupByDate()
'Sorts dates, then looks for date changes in column A and inserts
'blank color-filled row (cols A and B only) when date changes
Dim iRow As Long
Dim LastCell As Range
Dim DT As Date
'Sort columns A and B using col A (dates) as the key.
Set LastCell = Range("B65536").End(xlUp)
Range("A2", LastCell).Sort key1:=Range("A1")
'Now insert grouping separators
iRow = 2
Do
If Int(Cells(iRow + 1, "A")) > Int(Cells(iRow, "A")) Then
'date change occurred between iRow and iRow+1
iRow = iRow + 1
Rows(iRow).Insert
Range(Cells(iRow, "A"), Cells(iRow, "B")).Interior.Color = RGB(100, 100, 100)
ElseIf Int(Cells(iRow + 1, "A") * 24) > Int(Cells(iRow, "A") * 24) Then
'hour change occurred between iRow and iRow+1
iRow = iRow + 1
Rows(iRow).Insert
Range(Cells(iRow, "A"), Cells(iRow, "B")).Interior.Color = RGB(255, 255, 50)
End If
iRow = iRow + 1
Loop Until IsEmpty(Cells(iRow, "A"))
End Sub