Need help fixing a VBA code to show the correct date when time is being converted.

[spittingfire]

Hi All,

I have attached a workbook with some sample data to assist with a solution. The sheets in question are Sheet1 and Export.
With the data on Sheet1 I click on the "Automation" Tab. What that does is convert the times if needed and paste to the export sheet as per module 1 in the VBA. The issue is that it does correctly convert the times but not the date.
I don't know much about VBA but I suspect that something in the code of course is not correct.

As per the example in the sheet if Column I has "BURNABY" it will add 3 hours to covert to Eastern Time and if it as "MONCTON" it will subtract 1 hour to covert to Eastern time. The issue is for example as shown in the workbook book, "2015-04-21 00:58:00 is converted as 2015-04-21 23:58:00"

So the converted time of 23:58 is correct but the day is not - the day should have been changed to 2015-04-20.
Unfortunately I inherited this sheet and just found out that no one ever noticed this issue until I pointed it out. None of us there have the required knowledge to edit and make the correction.

Hence I am here looking for help from someone with the VBA knowledge to help me make the necessary corrections. In the sheet you can Clear the "Export" sheet and then on "Sheet1" click automation and you will see the results pasted there.

To the best of my knowledge all the VBA that is impacting the date is under Module1 "Private Function createImport()" but I could be wrong.

Any help would be most appreciated.

[Yongle]

I have not debugged the code to see why it was not amending the date. (When I get a chance, I will try to debug - away from PC for several days)
Instead, as a workaround, I added a few lines of code into the function createImport.
This appears to do the trick with the small amount of sample data, but suggest you test it with a lot more data. It amends the date in Column E.


If r.Offset(0, 8) = "MONCTON" Or r.Offset(0, 8) = "REMOTE AGENT - MONCTON" Then
If startTime >= "23:00" And startTime <= "23:59" Then
startDate = DateValue(nominalDate) - 1
Else
End If
End If


Spreadsheet attached with amended code.

[p45cal]

Yes, it looks like the problem is indeed in createImport; there seems to be lots of code trying to compensate for start and end times crossing over midnight and/or the time adjustment moving one or both times into another date.
The code is much more complex than it needs to be since the code has everywhere treated the date element and time element separately, which is not necessary. You can subtract/add hours to a single date variable (which contains time data too, and the date element will change if necessary automatically.
So before I simplify the code, I need to know in the Export sheet what's meant to go in columns D and E; in the code, Column D is nominaldate and column C is startdate and I'd like to know what the difference is meant to be in English (in the code there is a line which asks if the starttime is between midnight and 5am in which case the nominaldate (destined for column D) is changed to the day before - should this be kept in? I suspect this could be more straightforward).

Addendum:
Since writing the above I came across the following lines which may be another source of error:
If Hour(startTime) = 0 Then startTime = startTime - 3
If Hour(endTime) = 0 Then endTime = endTime - 3

At the time they're executed the variables startTime and endTime are full Date variables and they have 3 days subtracted, not 1 day, so where BURNABY is concerned when only one of the above If statements was true (where the start was between midnight and 1am or the end time was between midnight and 1am, but not both) you'll get some some Export sheets which might have a Duration column out by 2 or 3 days! I suspect it might not happen very often.

[spittingfire]

Thanks p45cal. So basically Columns D & E on the Export sheet should comes from the dates of Columns F & G from Sheet1. So when the conversion occurs I should either see the same date if a. it did not roll over into a new day, or b. a new day if the conversion rolls into a new day. So nominal date in Column D in the "Export" sheet should just be the start date where Column E in the "Export" sheet be the end date.

The line asking about the starttime between midnight and 5 am should not be necessary if we are able to have the dates corrected.

[spittingfire]

Thanks p45cal. So basically Columns D & E on the Export sheet should come from the dates of Columns F & G from Sheet1. So when the conversion occurs I should either see the same date if a. it did not roll over into a new day, or b. a new day if the conversion rolls into a new day. So nominal date in Column D in the "Export" sheet should just be the start date where Column E in the "Export" sheet be the end date.

The line asking about the starttime between midnight and 5 am should not be necessary if we are able to have the dates corrected.

[p45cal]

So to confirm, on the Export sheet:
Column D to contain the date portion of Sheet1 column F AFTER timezone conversion.
Column E to contain the date portion of Sheet1 column G AFTER timezone conversion.

(You realise that at the moment, the code derives both Export sheet columns D and E from column F of Sheet1? I haven't looked too closely to see if this ends up being the same. It's much easier if you can confirm the above.)

[spittingfire]

Yes that would be completely wrong. The Burnaby difference is only 3 hours and not 3 days.

[spittingfire]

Yes I do realise that and unless there end up being the same I think going with the below should be ok:

Column D to contain the date portion of Sheet1 column F AFTER timezone conversion.
Column E to contain the date portion of Sheet1 column G AFTER timezone conversion.

[spittingfire]

Hi p45cal, after taking another hard look at the data what I have concluded is that column D is just simply column F which is the 'nominal' date and column E is the conversion date also from column F. Column G is just used for the duration (End time - Start time). So that being said from an output perspective on the export sheet Column D will just be Column F from Sheet1 but column E will be the converted start date from Column F from sheet 1. Column F in export will be the converted time (BURNABY or MONCTON) while column G is the duration.

[p45cal]

Work in progress. This is a minimally tampered-with createImport macro. I called it createImport2. Add it to the same code-module and change the calling line in NTSDExceptionAutomation from createImport to createImport2. That way if it all goes tits up you can quickly revert to the original code simply by removing the 2 of createImport2 in NTSDExceptionAutomation.
There's a whole section on breaks which I've done nothing with yet, since I can't test; this section I haven't touched - it too could do with simplifying.
Note that if column D of the Export sheet is supposed to be the original date of the start time on sheet1, then it was not doing that in the original createimport. Createimport2 is doing that, so you'll find differences in that column too.
Differences in the Export sheet, createimport v createimport2 are in cells D1,D2,E1,E2,E4.

Private Function createImport2()
Dim r, a, b As Range
Dim i, breakcount As Integer
Dim id, checkid As String
Dim breakFound As Boolean
Dim nominalDate, startTime, endTime, startDate, duration, recordType As String
Dim oldNominalDate, oldStartTime, oldEndTime, oldStartDate, oldDuration As String
Sheets("Export").Cells.Delete (xlUp)
Sheets("Exception Number Report").Cells.Delete (xlUp)
Range("U:U").Replace What:=Chr(10), Replacement:=Chr(32)
i = 1
breakcount = 0

For Each r In Sheets("Sheet1").Range("A:A")
If r = "" Then Exit For
If Left(r, 3) = "EXC" Then
Debug.Assert r.Value <> "EXC-848817"
nominalDate = DateValue(r.Offset(, 5).Value) 'just the date part of column F of Sheet1.
startTime = CDate(r.Offset(, 5).Value) 'date AND time value of column F of Sheet1.
endTime = CDate(r.Offset(, 6).Value) 'date AND time value of column G of Sheet1.
duration = endTime - startTime

If r.Offset(0, 8) = "MONCTON" Or r.Offset(0, 8) = "REMOTE AGENT - MONCTON" Then
startTime = startTime - (1 / 24) 'date will be adjusted if necessary.
endTime = endTime - (1 / 24) 'date will be adjusted if necessary.
Sheets("Export").Cells(i, 1).EntireRow.Interior.ColorIndex = 36
ElseIf r.Offset(0, 8) = "BURNABY" Then
startTime = startTime + (3 / 24) 'date will be adjusted if necessary.
endTime = endTime + (3 / 24) 'date will be adjusted if necessary.
Sheets("Export").Cells(i, 1).EntireRow.Interior.ColorIndex = 36
ElseIf r.Offset(0, 8) = "Chat" Then
Sheets("Export").Cells(i, 1).EntireRow.Interior.ColorIndex = 21
End If
startDate = Int(startTime) 'just the date portion of the adjusted starttime.
startTime = startTime - startDate 'just the time portion of the adjusted starttime
id = r.Offset(0, 2)
checkid = id
For Each b In Sheets("Employee Number Master").Range("A:A")
If b = id Then
id = b.Offset(0, 1).Text
Exit For
End If
If b = "" Then Exit For
Next b
If checkid <> b Then id = id & "0"
recordType = r.Offset(0, 4)
If recordType = "PBRK1" Or recordType = "PBRK2" Or recordType = "PBRK3" Or recordType = "PBRK4" Or recordType = "UBRK1" Or recordType = "UBRK2" Then
'FIND PREVIOUS BREAK
On Error Resume Next
Set a = Sheets("Break Report").Columns(3).Find(What:=id, After:=Sheets("Break Report").Cells(1, 3), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)
On Error GoTo 0

If Not a Is Nothing Then
firstAddress = a.Address
Do
If a.Text = id Then
If a.Offset(0, 8) = recordType And DateValue(a.Offset(0, 7)) = DateValue(nominalDate) Then
breakFound = True
oldNominalDate = nominalDate
oldStartDate = Format(a.Offset(0, 9), "mm/dd/yyyy")
oldStartTime = Format(a.Offset(0, 9), "hh:mm")
oldDuration = CDate(Format("00:" & a.Offset(0, 11), "hh:mm"))
If oldStartTime >= CDate(Format("00:00", "hh:mm")) And oldStartTime < CDate(Format("05:00", "hh:mm")) Then
oldStartDate = oldNominalDate
oldNominalDate = DateValue(oldNominalDate) - 1
Else
startDate = oldNominalDate
End If
Exit Do
End If
End If
Set a = Sheets("Break Report").Columns(3).FindNext(a)
Loop While Not a Is Nothing And a.Address <> firstAddress


End If
If breakFound Then
Sheets("Export").Cells(i + 1, 1).EntireRow.Interior.ColorIndex = 44
If Left(recordType, 1) = "P" Then duration = "00:15"
If Left(recordType, 1) = "U" Then duration = "00:30"
With Sheets("Export")
.Cells(i, 1) = "10"
.Cells(i, 2) = id
.Cells(i, 3) = recordType
.Cells(i, 4) = oldNominalDate
.Cells(i, 5) = oldStartDate
.Cells(i, 6) = oldStartTime
.Cells(i, 7) = oldDuration
.Cells(i, 8) = ""
.Cells(i, 8).WrapText = False
End With
With Sheets("Export")
.Cells(i + 1, 1) = "11"
.Cells(i + 1, 2) = id
.Cells(i + 1, 3) = recordType
.Cells(i + 1, 4) = nominalDate
.Cells(i + 1, 5) = startDate
.Cells(i + 1, 6) = startTime
.Cells(i + 1, 7) = duration
.Cells(i + 1, 8) = "IMPORT " & r.Offset(0, 9)
.Cells(i + 1, 8).WrapText = False
End With
Sheets("Exception Number Report").Cells(i, 1).Value = r.Value
i = i + 2
End If
breakFound = False
Else
With Sheets("Export")
.Cells(i, 1) = "00"
.Cells(i, 2) = id
.Cells(i, 3) = recordType
.Cells(i, 4) = nominalDate
.Cells(i, 5) = startDate
.Cells(i, 6) = startTime
.Cells(i, 7) = duration
.Cells(i, 8) = "IMPORT " & r.Offset(0, 9)
.Cells(i, 8).WrapText = False
End With
Sheets("Exception Number Report").Cells(i, 1).Value = r.Value
i = i + 1
End If
End If
Next r
Sheets("Export").Cells.EntireColumn.AutoFit
Sheets("Exception Number Report").Cells.EntireColumn.AutoFit
End Function

[spittingfire]

Thanks a million p45cal. I will test and let you know my findings. Thanks also for the explanation in the code as it is rather informative and helpful.

One question I have however below I have highlighted the line. Is that line suppose to be there?

Private Function createImport2()
Dim r, a, b As Range
Dim i, breakcount As Integer
Dim id, checkid As String
Dim breakFound As Boolean
Dim nominalDate, startTime, endTime, startDate, duration, recordType As String
Dim oldNominalDate, oldStartTime, oldEndTime, oldStartDate, oldDuration As String
Sheets("Export").Cells.Delete (xlUp)
Sheets("Exception Number Report").Cells.Delete (xlUp)
Range("U:U").Replace What:=Chr(10), Replacement:=Chr(32)
i = 1
breakcount = 0

For Each r In Sheets("Sheet1").Range("A:A")
If r = "" Then Exit For
If Left(r, 3) = "EXC" Then
Debug.Assert r.Value <> "EXC-848817"
nominalDate = DateValue(r.Offset(, 5).Value) 'just the date part of column F of Sheet1.
startTime = CDate(r.Offset(, 5).Value) 'date AND time value of column F of Sheet1.
endTime = CDate(r.Offset(, 6).Value) 'date AND time value of column G of Sheet1.
duration = endTime - startTime

[p45cal]

Is that line supposed to be there?
No it's not!
It was there to stop the code when that record was reached so that I could step through the code…

[spittingfire]

I thought so but was just making sure

[spittingfire]

Hi p45cal,

I was testing the file today and ran into an issue. Now I know what the issue is but don't know how to go about correcting the error. I have highlighted the line in yellow for you to look at. The issue in this case is a duration went from 2015-04-27 16:00 to 2015-04-27 00:00. The issue is from the actual data the day did not roll over to 2015-04-28 00:00. I know that it meant for 2015-04-28 but our system exports it that way unfortunately. Is there a way to make excel convert those types of situations to show "next day" 00:00 instead of current day 00:00?

Thanks again in advance

[p45cal]

The issue is from the actual data the day did not roll over to 2015-04-28 00:00. I know that it meant for 2015-04-28 but our system exports it that way unfortunately. Is there a way to make excel convert those types of situations to show "next day" 00:00 instead of current day 00:00?
Wow!
Well if the end time is dead on midnight then the date/time combo will be an integer. We test for this integer with a view to adding a day:

If endTime = Int(endTime) Then endTime = endTime + 1

added after the line:

endTime = CDate(r.Offset(, 6).Value) 'date AND time value of column G of Sheet1.should do it.

It raises another question though: does the system export start times in the same way? If so you'll need to add a similar line for the starttime.

[spittingfire]

Thanks p45cal

That works!! woohoo

Yes I think I will also add to the stat time as well because I think such scenario can also occur.

Thanks again p45cal for your assistance

[spittingfire]

HI p45cal,

Once again I really appreciate all your help however I have hopefully one final request with regards to the file.

I have attached a new updated file and with the highlighted rows that are creating an issue.

The issues are with the end times being earlier than the start times. Looks like the original was just putting back incorrect data.

Is there a way to just have those rows deleted or skipped?

Thanks again in advance.

[p45cal]

Straight after:

duration = endTime - startTime
ask if duration is negative:

If duration < 0 Then '(or perhaps: If duration =< 0 Then)
'delete/ignore/do nothing/something else.
Else
'what you'd normally do
End If

CreateImport2 adjusted accordingly in attached.
On something different; if the start date is dead on midnight, what do you want to see in column D of Export?

Different again: The part of the code that deals with breaks still hasn't been looked at - is it generating duff data too?

[spittingfire]

Thanks p45cal

From what I have been told the only issue was the conversion for Burnaby and Moncton. I will continue to test but I think this may have done it.

Thanks again for all your assistance p45cal, much appreciated!