asked on

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

yo_bee

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 Prew

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

bfuchs

ASKER

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

yo_bee

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 Prew

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

ASKER CERTIFIED SOLUTION

Bill Prew

membership

This solution is only available to members.

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

Start Free Trial

bfuchs

ASKER

Thank you!

bfuchs

ASKER

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 Prew

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

SOLUTION

Bill Prew

membership

This solution is only available to members.

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

Start Free Trial

bfuchs

ASKER

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

bfuchs

ASKER

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 Prew

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

bfuchs

ASKER

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 Prew

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