Update time fields with AM/PM according to total hours column.

Hi Experts,

We have a system that exports time-sheets to CSV file, and from there it goes further...
There is also another column that includes the total hours.

Since users enter some times the timing with AM/PM and sometimes not.
We would like to have a function that will do the following.
Open that CSV file, update the time fields with AM/PM according to the total hours.

Would prefer either a VB Script or a VBA function.

PS. The Total hours column can be off with an hour or so (which is the lunch break).

Attaching example.

Thanks in advance.
Book1.csv
LVL 6
bfuchsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

yo_beeDirector of Information TechnologyCommented:
Does this system spit out DateTime or strictly time only?  
How do you plan to process this request.  Scheduled task, A trigger from an process?
Bill PrewIT / Software Engineering ConsultantCommented:
I'm a little confused on things like:

7:00      4:00            7.75

You mentioned the range could be off an hour for lunch, but this is off 1.25 hours, how is that handled?

Also, how would the code decide if this should be

7:00 AM      4:00 PM            7.75

or:

7:00 PM      4:00 AM            7.75

How is that decision to be made?


»bp
bfuchsAuthor Commented:
Hi Experts,

Does this system spit out DateTime or strictly time only?  
There is a separate column for the date.

How do you plan to process this request.  Scheduled task, A trigger from an process?
Lets say it will be a script, whenever user will click on that, it will convert a given file to the desired format.

You mentioned the range could be off an hour for lunch, but this is off 1.25 hours, how is that handled?
Actually per users, lunch time can be up to two hours.

How is that decision to be made?
In that case it would not matter as the calculation will be the same, so both can be converted to AM.

Note- the main purpose of this fix is, when exporting the times to a 3rd party, the calculation should be correct.

Thanks,
Ben
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

yo_beeDirector of Information TechnologyCommented:
Without the Date field it makes it very difficult to process this correctly.

How do you plan to process this request.  Scheduled task, A trigger from an process?
Lets say it will be a script, whenever user will click on that, it will convert a given file to the desired format.
Will a admin be handling this?
Bill PrewIT / Software Engineering ConsultantCommented:
Here is a VBS script that seems to work here.  If it can't determine an AM/PM pairing that is within 2 hours of the total time then it leaves them missing.  It also only processes input lines where both times are missing the AM/PM suffix.

Run as follows:

cscript EE29138740.vbs Book1.csv Book1.new

or:

cscript EE29138740.vbs Book1.csv

The first with read the first file and write the updated version to the second file.  The second form will read the first file, and overwrite it with the updated version.

Const ForReading = 1
Const ForWriting = 2
Const TriStateUseDefault = -2

Set objFSO = CreateObject("Scripting.FileSystemObject")

If (WScript.Arguments.Count > 0) Then
   strInFile = objFSO.GetAbsolutePathname(WScript.Arguments(0))
Else
   WScript.Echo "No input filename specified."
   WScript.Quit
End If

If (WScript.Arguments.Count > 1) Then
   strOutFile = WScript.Arguments(1)
   strOutFile = objFSO.GetAbsolutePathname(WScript.Arguments(1))
Else
   strOutFile = strInFile
End If

If Not objFSO.FileExists(strInFile) Then
   Wscript.Echo "ERROR: Input file """ & strInFile & """ does not exist."
   Wscript.Quit
End If

Set objFile = objFSO.OpenTextFile(strInFile, ForReading, False, TriStateUseDefault)
strData = objFile.ReadAll
objFile.Close

arrData = Split(strData, vbCrLf)

For i = 1 To UBound(arrData)

    If arrData(i) <> "" Then
        ' Split this input line into it's fields
        arrFields = Split(arrData(i), ",")
        
        ' Make sure we have at least 101 fields
        If UBound(arrFields) > 6 Then

            ' Look for matching field value and perform needed edit
            If Right(UCase(arrFields(4)), 1) <> "M" And Right(UCase(arrFields(5)), 1) <> "M" Then
                strAmPm = GetAmPm(arrFields(4), arrFields(5), arrFields(7))
                If strAmPm <> "" Then
                    arrFields(4) = arrFields(4) & " " & Left(strAmPm, 2)
                    arrFields(5) = arrFields(5) & " " & Right(strAmPm, 2)
                    arrData(i) = Join(arrFields, ",")
                End If
            End If

        End If

    End If

Next

Set objFile = objFSO.OpenTextFile(strOutFile, ForWriting, True)
objFile.Write Join(arrData, vbCrLf)
objFile.Close


Function GetAmPm(strStartTime, strEndTime, strTotalHours)
    Dim dblTotalHours, i, j, datStartTime, datEndTime, arrAmPm
    arrAmPm = Array("AM", "PM")

    dblTotalHours = CDbl(strTotalHours)
    GetAmPm = ""

    For i = 0 To 1
        For j = 0 To 1
            datStartTime = CDate("1/1/1900 " & strStartTime & " " & arrAmPm(i))
            datEndTime = CDate("1/1/1900 " & strEndTime & " " & arrAmPm(j))
            If datStartTime > datEndTime Then
                datEndTime = DateAdd("d", 1, datEndTime)
            End If
            If Abs(DateDiff("h", datStartTime, datEndTime) - dblTotalHours) <= 2 Then
                GetAmPm = arrAmPm(i) & arrAmPm(j)
                Exit Function
            End If
        Next
    Next
End Function

Open in new window



»bp
Bill PrewIT / Software Engineering ConsultantCommented:
And if you just want to hard code the file paths then this should work:

Const ForReading = 1
Const ForWriting = 2
Const TriStateUseDefault = -2

strInFile = "c:\temp\input.csv"
strOutFile = "c:\temp\output.csv"

Set objFSO = CreateObject("Scripting.FileSystemObject")
strInFile = objFSO.GetAbsolutePathname(strInFile)
strOutFile = objFSO.GetAbsolutePathname(strOutFile)

If Not objFSO.FileExists(strInFile) Then
   Wscript.Echo "ERROR: Input file """ & strInFile & """ does not exist."
   Wscript.Quit
End If

Set objFile = objFSO.OpenTextFile(strInFile, ForReading, False, TriStateUseDefault)
strData = objFile.ReadAll
objFile.Close

arrData = Split(strData, vbCrLf)

For i = 1 To UBound(arrData)

    If arrData(i) <> "" Then
        ' Split this input line into it's fields
        arrFields = Split(arrData(i), ",")
        
        ' Make sure we have at least 101 fields
        If UBound(arrFields) > 6 Then

            ' Look for matching field value and perform needed edit
            If Right(UCase(arrFields(4)), 1) <> "M" And Right(UCase(arrFields(5)), 1) <> "M" Then
                strAmPm = GetAmPm(arrFields(4), arrFields(5), arrFields(7))
                If strAmPm <> "" Then
                    arrFields(4) = arrFields(4) & " " & Left(strAmPm, 2)
                    arrFields(5) = arrFields(5) & " " & Right(strAmPm, 2)
                    arrData(i) = Join(arrFields, ",")
                End If
            End If

        End If

    End If

Next

Set objFile = objFSO.OpenTextFile(strOutFile, ForWriting, True)
objFile.Write Join(arrData, vbCrLf)
objFile.Close


Function GetAmPm(strStartTime, strEndTime, strTotalHours)
    Dim dblTotalHours, i, j, datStartTime, datEndTime, arrAmPm
    arrAmPm = Array("AM", "PM")

    dblTotalHours = CDbl(strTotalHours)
    GetAmPm = ""

    For i = 0 To 1
        For j = 0 To 1
            datStartTime = CDate("1/1/1900 " & strStartTime & " " & arrAmPm(i))
            datEndTime = CDate("1/1/1900 " & strEndTime & " " & arrAmPm(j))
            If datStartTime > datEndTime Then
                datEndTime = DateAdd("d", 1, datEndTime)
            End If
            If Abs(DateDiff("h", datStartTime, datEndTime) - dblTotalHours) <= 2 Then
                GetAmPm = arrAmPm(i) & arrAmPm(j)
                Exit Function
            End If
        Next
    Next
End Function

Open in new window


»bp

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bfuchsAuthor Commented:
Thank you!
bfuchsAuthor Commented:
Hi Bill,

Actually just tested with actual file, where all columns contain data, not only those 3 as in the attached, and getting an error.
See attached.

PS.Its the column D (StartDate containing dates) that is causing the error.

Thanks,
Ben
Untitled.png
Bill PrewIT / Software Engineering ConsultantCommented:
I would need to see the input data line that caused that error.

The code I provided made some assumptions about there being valid data in the columns we were working with, and the input data being well formed, it sounds like that may not be the case, so I would need a test file that demonstrates the conditions(s) that need to be dealt with...


»bp
Bill PrewIT / Software Engineering ConsultantCommented:
Here is a version that skips any input records if any of the three needed columns (StartTime, EndTime, TotalHours) are blank.  Should resolve the error.

Const ForReading = 1
Const ForWriting = 2
Const TriStateUseDefault = -2

strInFile = "B:\EE\EE29138740\Book1.csv"
strOutFile = "B:\EE\EE29138740\Book1.new"

Set objFSO = CreateObject("Scripting.FileSystemObject")
strInFile = objFSO.GetAbsolutePathname(strInFile)
strOutFile = objFSO.GetAbsolutePathname(strOutFile)

If Not objFSO.FileExists(strInFile) Then
   Wscript.Echo "ERROR: Input file """ & strInFile & """ does not exist."
   Wscript.Quit
End If

Set objFile = objFSO.OpenTextFile(strInFile, ForReading, False, TriStateUseDefault)
strData = objFile.ReadAll
objFile.Close

arrData = Split(strData, vbCrLf)

For i = 1 To UBound(arrData)

wscript.echo i & " [" & arrData(i) & "]"

    If arrData(i) <> "" Then
        ' Split this input line into it's fields
        arrFields = Split(arrData(i), ",")
        
        ' Make sure we have at least 101 fields
        If UBound(arrFields) > 6 Then

            If arrFields(4) <> "" And arrFields(5) <> "" And arrFields(7) <> "" Then

                ' Look for matching field value and perform needed edit
                If Right(UCase(arrFields(4)), 1) <> "M" And Right(UCase(arrFields(5)), 1) <> "M" Then
                    strAmPm = GetAmPm(arrFields(4), arrFields(5), arrFields(7))
                    If strAmPm <> "" Then
                        arrFields(4) = arrFields(4) & " " & Left(strAmPm, 2)
                        arrFields(5) = arrFields(5) & " " & Right(strAmPm, 2)
                        arrData(i) = Join(arrFields, ",")
                    End If
                End If

            End If

        End If

    End If

Next

Set objFile = objFSO.OpenTextFile(strOutFile, ForWriting, True)
objFile.Write Join(arrData, vbCrLf)
objFile.Close


Function GetAmPm(strStartTime, strEndTime, strTotalHours)
    Dim dblTotalHours, i, j, datStartTime, datEndTime, arrAmPm
    arrAmPm = Array("AM", "PM")

    dblTotalHours = CDbl(strTotalHours)
    GetAmPm = ""

    For i = 0 To 1
        For j = 0 To 1
            datStartTime = CDate("1/1/1900 " & strStartTime & " " & arrAmPm(i))
            datEndTime = CDate("1/1/1900 " & strEndTime & " " & arrAmPm(j))
            If datStartTime > datEndTime Then
                datEndTime = DateAdd("d", 1, datEndTime)
            End If
            If Abs(DateDiff("h", datStartTime, datEndTime) - dblTotalHours) <= 2 Then
                GetAmPm = arrAmPm(i) & arrAmPm(j)
                Exit Function
            End If
        Next
    Next
End Function


Sub SavedCode()
    Set objFSO = CreateObject("Scripting.FileSystemObject")

    If (WScript.Arguments.Count > 0) Then
       strInFile = objFSO.GetAbsolutePathname(WScript.Arguments(0))
    Else
       WScript.Echo "No input filename specified."
       WScript.Quit
    End If

    If (WScript.Arguments.Count > 1) Then
       strOutFile = WScript.Arguments(1)
       strOutFile = objFSO.GetAbsolutePathname(WScript.Arguments(1))
    Else
       strOutFile = strInFile
    End If

    If Not objFSO.FileExists(strInFile) Then
       Wscript.Echo "ERROR: Input file """ & strInFile & """ does not exist."
       Wscript.Quit
    End If
End Sub

Open in new window


»bp
bfuchsAuthor Commented:
Hi,
You left some debugging messages-)?
See attached whats keeps popping up for each line...
btw, how do I stop this from running? (tried killing from task manager but still pops up.)
Thanks,
Ben
Untitled.png
bfuchsAuthor Commented:
Actually I commented out the code providing the message.
Just need a way to kill this running process...
I see this guy had the same issue, but solution provided there did not help here..
Thanks,
Ben
Bill PrewIT / Software Engineering ConsultantCommented:
Yes, comment it out, sorry.

CTRL-C if it's running from command shell.

Or find the process from Task Manager and kill it (likely WSCRIPT).


»bp
bfuchsAuthor Commented:
its not running from command shell.
under task manager I only see Windows script host, when I kill that the next message in loop pops up.
Thanks,
Ben
Bill PrewIT / Software Engineering ConsultantCommented:
There could be a process for the popup message as well as the original vbs script, you may need to kill both.

This is why I always run these type of scripts via CSCRIPT rather than just running the VBS which runs it via WSCRIPT, and displays output in individual message boxes rather than lines on the command window.


»bp
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.