Update Lunch column break according to schedule start/end time.

Hi Experts,

This is in reference to the following.
I would like to have the lunch column getting populated with the correct amount.

Thanks in advance.
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.

Martin LissOlder than dirtCommented:
Please attach a sample workbook.
bfuchsAuthor Commented:
Hi Martin,
Everything is included in the other thread, the sample workbook along with the current logic used for calculating times, we're just missing one column which is the lunch break.
Thanks,
Ben
Martin LissOlder than dirtCommented:
Please re-attach the sample workbook from that thread because I don't see it.
Ensure Business Longevity with As-A-Service

Using the as-a-service approach for your business model allows you to grow your revenue stream with new practice areas, without forcing you to part ways with existing clients just because they don’t fit the mold of your new service offerings.

bfuchsAuthor Commented:
Attached.

Thanks,
Ben
Book4.csv
Bill PrewIT / Software Engineering ConsultantCommented:
Give this a try, seems to be working here...

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

strInFile = "B:\EE\EE29139695\Book4.csv"
strOutFile = "B:\EE\EE29139695\Book4.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)

    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)
                    End If
                End If

                ' Calculate lunch hours
                arrFields(6) = CalcLunchTime(arrFields(4), arrFields(5), arrFields(7))
                arrData(i) = Join(arrFields, ",")

            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

Function CalcLunchTime(strStartTime, strEndTime, strTotalHours)
    Dim dblTotalHours, datStartTime, datEndTime

    dblTotalHours = CDbl(strTotalHours)
    datStartTime = CDate("1/1/1900 " & strStartTime)
    If Right(strStartTime, 2) = "PM" And Right(strEndTime, 2) = "AM" Then
        datEndTime = CDate("1/2/1900 " & strEndTime)
    Else
        datEndTime = CDate("1/1/1900 " & strEndTime)
    End If
    CalcLunchTime = DateDiff("h", datStartTime, datEndTime) - dblTotalHours
End Function

Open in new window


»bp
bfuchsAuthor Commented:
Hi,

Mostly are fine.
However some numbers are negative, see below.

StartTime      EndTime      Lunch      TotalHours      
9:00 AM      6:30 PM      -0.5      9.5       
7:00 AM      7:30 PM      -0.5      12.5       
7:15 AM      12:15 AM      -21      14       
8:00 AM      12:00 AM      -22      14       
8:00 AM      3:15 PM      -0.25      7.25       
8:00 AM      3:30 PM      -0.5      7.5       
8:00 AM      4:15 PM      -0.25      8.25       
7:00 AM      11:45 AM      -0.75      4.75       

Thanks,
Ben
Bill PrewIT / Software Engineering ConsultantCommented:
Okay, give this adjustment a try:

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

strInFile = "B:\EE\EE29139695\Book4.csv"
strOutFile = "B:\EE\EE29139695\Book4.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)

    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)
                    End If
                End If

                ' Calculate lunch hours
                arrFields(6) = CalcLunchTime(arrFields(4), arrFields(5), arrFields(7))
                arrData(i) = Join(arrFields, ",")

            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

Function CalcLunchTime(strStartTime, strEndTime, strTotalHours)
    Dim dblTotalHours, datStartTime, datEndTime

    dblTotalMins = CDbl(strTotalHours) * 60
    datStartTime = CDate("1/1/1900 " & strStartTime)
    If Right(strStartTime, 2) = "PM" And Right(strEndTime, 2) = "AM" Then
        datEndTime = CDate("1/2/1900 " & strEndTime)
    Else
        datEndTime = CDate("1/1/1900 " & strEndTime)
    End If
    CalcLunchTime = (DateDiff("n", datStartTime, datEndTime) - dblTotalMins) / 60
    Wscript.Echo strStartTime & ", " & strEndTime & ", " & datStartTime & ", " & datEndTime  & ", " & DateDiff("n", datStartTime, datEndTime) & ", " & dblTotalMins & ", " & CalcLunchTime
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 Bill,
Not in the office.
Will do it on Monday.
Have a nice weekend.
Thanks,
Ben
bfuchsAuthor Commented:
Hi Bill,

We are still getting negative values for lunch (3rd column), see below.

7:15 AM      12:15 AM      -21      14
8:00 AM      12:00 AM      -22      14
7:30 AM      12:00 AM      -23      15.5

Thanks,
Ben
bfuchsAuthor Commented:
In addition, the following records didn't get lunch, neither did they got AM/PM...

3:00      11:30            7.5
11:30      7:30            6

Thanks,
Ben
Bill PrewIT / Software Engineering ConsultantCommented:
Okay, I'll have to revisit the logic I'm using and shake out the issues...


»bp
Bill PrewIT / Software Engineering ConsultantCommented:
So can we clarify something?  Will the input start and end times in the file being read have AM and PM already on all values?  Or will there be no AM and PM on any values?  Or is there a mix?

The way you have the data structured with a start date but no end date makes this a challenging question, and some assumptions have to be made.  I want to make sure I know what the incoming data will always look like so I make the right assumptions.


»bp
Bill PrewIT / Software Engineering ConsultantCommented:
While I'm waiting for those answers, here's a slightly different approach that seems to be working a bit better with the test data, if you want to try it there...

Option Explicit

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

Dim strInFile, strOutFile
Dim objFSO, objFile, strData, arrData, arrFields, i
Dim blnAmPm, datStartTime, datEndTime

strInFile = "B:\EE\EE29139695\Book4.csv"
strOutFile = "B:\EE\EE29139695\Book4.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)

    If arrData(i) <> "" Then
        ' Split this input line into it's fields
        arrFields = Split(arrData(i), ",")
        
        ' Wscript.Echo "---------------------------------------------------------------------------------------"        

        ' Make sure we have at least 101 fields
        If UBound(arrFields) > 6 Then

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

                datStartTime = CDate(arrFields(3) & " " & arrFields(4))
                datEndTime = CDate(arrFields(3) & " " & arrFields(5))

                blnAmPm = GetAmPm(datStartTime, datEndTime, arrFields(7))

                If blnAmPm Then
                    arrFields(4) = FormatDateTime(datStartTime, 3)
                    arrFields(5) = FormatDateTime(datEndTime, 3)

                    ' Calculate lunch hours
                    arrFields(6) = CalcLunchTime(datStartTime, datEndTime, arrFields(7))
                    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(ByRef datStartTime, ByRef datEndTime, strTotalHours)
    Dim datTempEndTime, dblTotalMins, i, d

    dblTotalMins = CDbl(strTotalHours) * 60
    GetAmPm = False

    For i = 0 To 24 Step 12
        datTempEndTime = DateAdd("h", i, datEndTime)
        ' Wscript.Echo "GetAmPm : " & datStartTime & ", " & datTempEndTime & ", " & DateDiff("n", datStartTime, datTempEndTime) & ", " & dblTotalMins
        If datStartTime <= datTempEndTime Then
            d = DateDiff("n", datStartTime, datTempEndTime) - dblTotalMins
            If d >= 0 And d <= 120 Then
                GetAmPm = True
                datEndTime = datTempEndTime
                Exit Function
            End If
        End If
    Next
End Function

Function CalcLunchTime(datStartTime, datEndTime, strTotalHours)
    Dim dblTotalMins

    dblTotalMins = CDbl(strTotalHours) * 60
    CalcLunchTime = (DateDiff("n", datStartTime, datEndTime) - dblTotalMins) / 60

    ' Wscript.Echo "CalcLunchTime : " & datStartTime & ", " & datEndTime & ", " & DateDiff("n", datStartTime, datEndTime) & ", " & dblTotalMins & ", " & CalcLunchTime
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:
Hi,

Will the input start and end times in the file being read have AM and PM already on all values?  Or will there be no AM and PM on any values?  Or is there a mix?
There will be a mix as original data comes from from two sources, one with AM/PM and one w/o them.
The way you have the data structured with a start date but no end date
Are you referring to Start/End times? In cases where there is no start or end time I guess you can ignore them and no need to do anything.
...makes this a challenging question
If that turns out to be quite challenging perhaps would close the question and open another one on the remaining issues so you can be awarded properly.
if you want to try it there...
I can test for errors or just randomly but real testing would have to wait for users tom.

Thanks,
Ben
bfuchsAuthor Commented:
while testing this new version I came across these records.

7:00 PM      12:00      -12      5      

which should be filled with 12AM and no lunch.

and
 11:45      8:00      -10      6.25
which should be either AM or PM, and lunch 2 hours.

P.S. I might be wrong, so perhaps lets wait for users to say their input.

Thanks,
Ben
Bill PrewIT / Software Engineering ConsultantCommented:
When I ran these two test case through the latest version I sent you:

,a,b,2/1/2019,7:00 PM,12:00,,5,,,
,a,b,2/1/2019,11:45,8:00,,6.25,,,

Open in new window

I got the correct output I believe of:

,a,b,2/1/2019,7:00:00 PM,12:00:00 AM,0,5,,,
,a,b,2/1/2019,11:45:00 AM,8:00:00 PM,2,6.25,,,

Open in new window


»bp
bfuchsAuthor Commented:
Hi Bill,
Not sure why it didn't show on mine yesterday but now it works.
Thank you!
Bill PrewIT / Software Engineering ConsultantCommented:
Welcome.


»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
System Programming

From novice to tech pro — start learning today.