Avatar of bfuchs
bfuchs
Flag for United States of America 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
VB ScriptMicrosoft OfficeVBAMicrosoft Excel

Avatar of undefined
Last Comment
Bill Prew

8/22/2022 - Mon
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
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
bfuchs

ASKER
Thank you!
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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