Billa7
asked on
Convert Date error
Please help fix this error, the script unable to convert the date after passing the midnight (00:00), I have highlighted this error in "yellow". By right the date at cell B8 should be updated with "13-Mar" and cell B21 with " 14-Mar". Hope Experts able to fix this .
Sub ConvertData()
With Sheet1
For I = 2 To .UsedRange.Rows.Count
If .Cells(I, 3) = "" Then
Exit For
End If
.Cells(I, 1) = .Cells(I, 3) + 8 / 24
If Hour(.Cells(I, 1)) < Hour(.Cells(I, 3)) Then
.Cells(I, 2) = .Cells(I, 2) + 1
End If
Next I
End With
End Sub
Time.xls
ASKER
Hi Zack Barresse,
I have tested by replacing this line:
"If .Cells(I, 3) = 0 Then" and it's only update cell from A2:A7, other cells are remained empty.
I have tested by replacing this line:
"If .Cells(I, 3) = 0 Then" and it's only update cell from A2:A7, other cells are remained empty.
Maybe closer to what you have then. I'm not sure I understand the logic here or where the values are coming from...
Sub ConvertData_Updated()
Dim WS As Worksheet
Dim iLastRow As Long
Dim i As Long
Application.ScreenUpdating = False
Set WS = ThisWorkbook.Worksheets("D ata")
iLastRow = WS.Range("C:C").Find(What: ="*", After:=WS.Range("C1"), LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPreviou s).Row
For i = 2 To iLastRow Step 1
WS.Cells(i, 1).Value = WS.Cells(i, 3).Value + TimeValue("08:00")
If VBA.Hour(WS.Cells(i, 1).Value) < VBA.Hour(WS.Cells(i, 3).Value) Then
WS.Cells(i, 2).Value = WS.Cells(i, 2).Value + 1
End If
Next i
WS.Range("A2:A" & iLastRow).NumberFormat = "h:mm"
WS.Range("B2:B" & iLastRow).NumberFormat = "dd-mmm-yyyy"
WS.Range("C2:C" & iLastRow).NumberFormat = "h:mm"
Application.ScreenUpdating = True
End Sub
HTH
Regards,
Zack Barresse
Sub ConvertData_Updated()
Dim WS As Worksheet
Dim iLastRow As Long
Dim i As Long
Application.ScreenUpdating
Set WS = ThisWorkbook.Worksheets("D
iLastRow = WS.Range("C:C").Find(What:
For i = 2 To iLastRow Step 1
WS.Cells(i, 1).Value = WS.Cells(i, 3).Value + TimeValue("08:00")
If VBA.Hour(WS.Cells(i, 1).Value) < VBA.Hour(WS.Cells(i, 3).Value) Then
WS.Cells(i, 2).Value = WS.Cells(i, 2).Value + 1
End If
Next i
WS.Range("A2:A" & iLastRow).NumberFormat = "h:mm"
WS.Range("B2:B" & iLastRow).NumberFormat = "dd-mmm-yyyy"
WS.Range("C2:C" & iLastRow).NumberFormat = "h:mm"
Application.ScreenUpdating
End Sub
HTH
Regards,
Zack Barresse
ASKER
Hi Zack Barresse,
Sorry if request is really confusing you. The revised code still giving me a wrong date update. I've highlighted this error in yellow. Attached the workbook for your kind reference.
Time.xls
Sorry if request is really confusing you. The revised code still giving me a wrong date update. I've highlighted this error in yellow. Attached the workbook for your kind reference.
Time.xls
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi,
Thanks a lot for the help
Thanks a lot for the help
If .Cells(I, 3) = 0 Then
Of course, if you wanted a more precise time format, because you could be shaving minutes off by doing the course math manually, you could use actual time values ...
.Cells(i, 1) = .Cells(i, 3) + TimeValue("08:00")
HTH
Regards,
Zack Barresse