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.
This is in reference to the following.
I would like to have the lunch column getting populated with the correct amount.
Thanks in advance.
Protect yourself and your loved ones. Stay home for the holidays.
CERTIFIED EXPERT
Please attach a sample workbook.
CERTIFIED EXPERT
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
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
Protect yourself and your loved ones. Stay home for the holidays.
CERTIFIED EXPERT
Please re-attach the sample workbook from that thread because I don't see it.
Give this a try, seems to be working here...
»bp
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
»bp
CERTIFIED EXPERT
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
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
Okay, give this adjustment a try:
»bp
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
»bp
CERTIFIED EXPERT
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
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
CERTIFIED EXPERT
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
3:00 11:30 7.5
11:30 7:30 6
Thanks,
Ben
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
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
CERTIFIED EXPERT
Hi,
Thanks,
Ben
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 dateAre 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 questionIf 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
CERTIFIED EXPERT
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
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
When I ran these two test case through the latest version I sent you:
»bp
,a,b,2/1/2019,7:00 PM,12:00,,5,,,
,a,b,2/1/2019,11:45,8:00,,6.25,,,
,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,,,
»bp
CERTIFIED EXPERT
Hi Bill,
Not sure why it didn't show on mine yesterday but now it works.
Thank you!
Not sure why it didn't show on mine yesterday but now it works.
Thank you!
CERTIFIED EXPERT
Hi Bill,
Would prefer to add something to this script.
Are you avail for a new Q?
Thanks,
Ben
Would prefer to add something to this script.
Are you avail for a new Q?
Thanks,
Ben
Gain unlimited access to on-demand training courses with an Experts Exchange subscription.
Get AccessWhy 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
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