We help IT Professionals succeed at work.

# Convert Date error

on
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
Comment
Watch Question

## View Solution Only

BRONZE EXPERT

Commented:
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

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.
BRONZE EXPERT

Commented:
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

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
BRONZE EXPERT
Commented:
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

Commented:
Hi,

Thanks a lot for the help