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
Billa7Asked:
Who is Participating?
 
Zack BarresseConnect With a Mentor CEOCommented:
I *think* I see what you're talking about.  Try this revision, and if it's not giving you what you want, please give a more detailed description.  It appears that if the time is midnight and the updated time (+8) is after midnight (i.e. the next day) you want the date to move forward.  Is that correct?  Also, the dates basically are incrementing when you run this code by one day.  I'm not following the logic, so an explanation would help.

To get the day to increase if the above is the case, replace this line of code ...

If VBA.Hour(WS.Cells(i, 1).Value) < VBA.Hour(WS.Cells(i, 3).Value) Then

... with this line of code ...

If VBA.Hour(WS.Cells(i, 1).Value) < VBA.Hour(WS.Cells(i, 3).Value) Or WS.Cells(i, 3).Value = 0 Then

HTH

Regards,
Zack Barresse
0
 
Zack BarresseCEOCommented:
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
0
 
Billa7Author Commented:
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.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Zack BarresseCEOCommented:
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
0
 
Billa7Author Commented:
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
0
 
Billa7Author Commented:
Hi,

Thanks a lot for the help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.