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

Open in new window

Time.xls

Zack Barresse

If it is a zero value, i.e. has time value only and shows 12:00 AM, then just check for a zero value...

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

Billa7

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.

Zack Barresse

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("Data")
iLastRow = WS.Range("C:C").Find(What:="*", After:=WS.Range("C1"), LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).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

Billa7

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

ASKER CERTIFIED SOLUTION

Zack Barresse

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Billa7

ASKER

Hi,

Thanks a lot for the help