We help IT Professionals succeed at work.

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

352 Views
Last Modified: 2019-03-11
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
Comment
Watch Question

yo_beeDirector of Information Technology
CERTIFIED EXPERT

Commented:
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 PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

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

Author

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
yo_beeDirector of Information Technology
CERTIFIED EXPERT

Commented:
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 PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
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
Test your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT

Author

Commented:
Thank you!
CERTIFIED EXPERT

Author

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 PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
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 PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT

Author

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
CERTIFIED EXPERT

Author

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 PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

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

Author

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 PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.