There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.
Calculate no. of hours at a time of day
My application is about booking faults and then closing them when they have been resolved.
I need to calculate how long a fault has been open from the Time that it was assigned to a Team to the current Time or time it was closed.
Here's the tricky part:
1. The calculation should only calculate for the time period 08H00 to 16H00 of the week day, i.e. Monday to Friday. Example if I log a fault on Thursday at 15H30, the duration that it is open for is an hour at 08H00 on Friday morning.
2. The calculation should exclude weekends, i.e. Saturday and Sunday.
3. The calculation should exclude public holidays. (some where I have to define these days in order for my system to pick it up)
Any ideas as to how the problem will be solved?
Thnx
I need to calculate how long a fault has been open from the Time that it was assigned to a Team to the current Time or time it was closed.
Here's the tricky part:
1. The calculation should only calculate for the time period 08H00 to 16H00 of the week day, i.e. Monday to Friday. Example if I log a fault on Thursday at 15H30, the duration that it is open for is an hour at 08H00 on Friday morning.
2. The calculation should exclude weekends, i.e. Saturday and Sunday.
3. The calculation should exclude public holidays. (some where I have to define these days in order for my system to pick it up)
Any ideas as to how the problem will be solved?
Thnx
Asked:
-
25
-
20
-
4
- +1
1 Solution
Whoops - that shoud be
Public Function BookedHours(StartTime As String, StartDate As String, EndTime As String, EndDate As String) As String
- to get a numeric result back (in minutes), keep the
Public Function BookedHours(StartTime As String, StartDate As String, EndTime As String, EndDate As String) As Long
and change
BookedHours = CStr((intDays*HOURS_IN_DAY
To
BookedHours = (((intDays*HOURS_IN_DAY)+i
HTH
Simon
Public Function BookedHours(StartTime As String, StartDate As String, EndTime As String, EndDate As String) As String
- to get a numeric result back (in minutes), keep the
Public Function BookedHours(StartTime As String, StartDate As String, EndTime As String, EndDate As String) As Long
and change
BookedHours = CStr((intDays*HOURS_IN_DAY
To
BookedHours = (((intDays*HOURS_IN_DAY)+i
HTH
Simon
Interesting... Grizzle has a similar problem in Q10314464 (http://www.experts-exchange.com/jsp/qShow.jsp?ta=msaccess&qid=10314464 ).
I'll offer to you, as I did to him, the opportunity for me to e-mail a solution I engineered recently. (I haven't sent it to him yet, either, as I haven't gotten enough time to extract the functionality from the other app into a new, stand-alone mdb yet...)
inimeq: If you are interested in seeing my solution, let me know in this forum.
Experts: Yes, I'll clue y'all in on how it works!
I'll offer to you, as I did to him, the opportunity for me to e-mail a solution I engineered recently. (I haven't sent it to him yet, either, as I haven't gotten enough time to extract the functionality from the other app into a new, stand-alone mdb yet...)
inimeq: If you are interested in seeing my solution, let me know in this forum.
Experts: Yes, I'll clue y'all in on how it works!
Simonbennet:
Your solution is pretty slick and straightforward. In fact, I swiped it...
I modified your function to fit an application I'm working on. I changed it so that it accepts two date values: dtmStart and dtmEnd. Then in the function I calculate StartTime, StartDate, EndTime, and EndDate.
I gained a slight improvement in speed (I'm running this in a query with thousands of records) by changing the 4 string variables to dates.
Wes
Your solution is pretty slick and straightforward. In fact, I swiped it...
I modified your function to fit an application I'm working on. I changed it so that it accepts two date values: dtmStart and dtmEnd. Then in the function I calculate StartTime, StartDate, EndTime, and EndDate.
I gained a slight improvement in speed (I'm running this in a query with thousands of records) by changing the 4 string variables to dates.
Wes
Hi Simon
I haven't forgotten about the question. I've been so busy meeting other project deadlines. :-(
As yet I haven't had the chance to evaluate the answer you provided.
I don't doubt it's "correctness" but I'd like to test it before I close the question off.
I'll tackle it the weekend :-)
Regards,
inimeg
I finally had a chance to run your function :-)
I get my input values, StartDate and EndDate, from a screen. The screen has a command button which, when clicked, passes these values to your function.
I entered the following values:
StartDate: 17/03/2000 09:00
EndDate : 17/03/2000 13:00
The answer I get for BookedHours = -240 which should actually be 4.
Is there a step of code missing or a check that must be built in?
Maybe I'm doing something wrong. Should the check for "on the same day" be before the For ...next loop?
The code does not pick up the Working End Time (e.g. 16:00)
If I enter an End Time of 17h00 with a Start Time of 09h00 for the same day, the BookedHours = 8 instead of 7.
Help ... whats wrong?
I'm referring to the code provided by simonbennett.
I did send you e-mail, Beliver w.r.t. this question.
I did send you e-mail, Beliver w.r.t. this question.
If you used the code exactly as it appears you can't feed it an end time past 16:00. That is the end of the workday so any time/date outside of the valid workdays will fail.
I don't want to step on SimonBennett's toes, but here's my modified version of his code. I think the original had problems with times on the same date:
Public Function WorkMinutes(dtmStart As Date, dtmEnd As Date) As Long
' constants that indicate the working day
Const WORKING_DAY_START = "8:00"
Const WORKING_DAY_END = "17:30"
' misc temp variables
Dim intMinutes As Integer
Dim intHours As Integer
Dim intDays As Integer
Dim HOURS_IN_DAY As Integer
Dim MINUTES_IN_DAY As Integer
Dim booInd As Boolean
Dim intCount As Integer
Dim intResult As Integer
Dim snpCheck As Recordset
intMinutes = 0
intHours = 0
intDays = 0
Dim StartTime As Date, StartDate As Date, EndTime As Date, EndDate As Date
StartTime = TimeValue(dtmStart)
StartDate = DateValue(dtmStart)
EndTime = TimeValue(dtmEnd)
EndDate = DateValue(dtmEnd)
HOURS_IN_DAY = DateDiff("h", WORKING_DAY_START, WORKING_DAY_END)
MINUTES_IN_DAY = DateDiff("m", WORKING_DAY_START, WORKING_DAY_END)
' work out the number of days excluding weekends
For intCount = 0 To DateDiff("d", StartDate, EndDate) - 1
intResult = WeekDay(DateAdd("d", intCount, StartDate))
If intResult <> vbSunday And intResult <> vbSaturday Then
Set snpCheck = CurrentDb().OpenRecordset(
If snpCheck.RecordCount = 0 Then
intDays = intDays + 1
End If
snpCheck.close
End If
Next
' work out the number of hours, bearing in mind we already have the number of days
If StartDate <> EndDate Then ' we spread over days
intDays = intDays - 1
' workout hours on first day
intHours = intHours + DateDiff("h", StartTime, WORKING_DAY_END)
intMinutes = intMinutes + DateDiff("n", StartTime, WORKING_DAY_END) Mod 60
' workout hours on last day
intHours = intHours + DateDiff("h", WORKING_DAY_START, EndTime)
intMinutes = intMinutes + DateDiff("n", WORKING_DAY_START, EndTime) Mod 60
' any days in between?
If intDays > 0 Then
intHours = intHours + ((intDays - 1) * HOURS_IN_DAY)
intMinutes = intMinutes + ((intDays - 1) * (MINUTES_IN_DAY Mod 60))
End If
Else ' we are on the same day
intHours = DateDiff("h", StartTime, EndTime)
intMinutes = DateDiff("n", StartTime, EndTime) Mod 60
If intMinutes > 0 And intHours > 1 Then
intHours = intHours - 1
End If
End If
' finally, tidy up the result
If intHours >= HOURS_IN_DAY Then ' add to the days
intDays = intDays + (intHours / HOURS_IN_DAY)
intHours = intHours Mod HOURS_IN_DAY
End If
If intMinutes >= 60 Then ' add to the days
intHours = intHours + (intMinutes / 60)
intMinutes = intMinutes Mod 60
End If
'BookedHours = CStr((intDays * HOURS_IN_DAY) + intHours) & " hours, " & CStr(intMinutes) & " minutes"
WorkMinutes = (((intDays * HOURS_IN_DAY) + intHours) * 60) + intMinutes
End Function
Wes
I don't want to step on SimonBennett's toes, but here's my modified version of his code. I think the original had problems with times on the same date:
Public Function WorkMinutes(dtmStart As Date, dtmEnd As Date) As Long
' constants that indicate the working day
Const WORKING_DAY_START = "8:00"
Const WORKING_DAY_END = "17:30"
' misc temp variables
Dim intMinutes As Integer
Dim intHours As Integer
Dim intDays As Integer
Dim HOURS_IN_DAY As Integer
Dim MINUTES_IN_DAY As Integer
Dim booInd As Boolean
Dim intCount As Integer
Dim intResult As Integer
Dim snpCheck As Recordset
intMinutes = 0
intHours = 0
intDays = 0
Dim StartTime As Date, StartDate As Date, EndTime As Date, EndDate As Date
StartTime = TimeValue(dtmStart)
StartDate = DateValue(dtmStart)
EndTime = TimeValue(dtmEnd)
EndDate = DateValue(dtmEnd)
HOURS_IN_DAY = DateDiff("h", WORKING_DAY_START, WORKING_DAY_END)
MINUTES_IN_DAY = DateDiff("m", WORKING_DAY_START, WORKING_DAY_END)
' work out the number of days excluding weekends
For intCount = 0 To DateDiff("d", StartDate, EndDate) - 1
intResult = WeekDay(DateAdd("d", intCount, StartDate))
If intResult <> vbSunday And intResult <> vbSaturday Then
Set snpCheck = CurrentDb().OpenRecordset(
If snpCheck.RecordCount = 0 Then
intDays = intDays + 1
End If
snpCheck.close
End If
Next
' work out the number of hours, bearing in mind we already have the number of days
If StartDate <> EndDate Then ' we spread over days
intDays = intDays - 1
' workout hours on first day
intHours = intHours + DateDiff("h", StartTime, WORKING_DAY_END)
intMinutes = intMinutes + DateDiff("n", StartTime, WORKING_DAY_END) Mod 60
' workout hours on last day
intHours = intHours + DateDiff("h", WORKING_DAY_START, EndTime)
intMinutes = intMinutes + DateDiff("n", WORKING_DAY_START, EndTime) Mod 60
' any days in between?
If intDays > 0 Then
intHours = intHours + ((intDays - 1) * HOURS_IN_DAY)
intMinutes = intMinutes + ((intDays - 1) * (MINUTES_IN_DAY Mod 60))
End If
Else ' we are on the same day
intHours = DateDiff("h", StartTime, EndTime)
intMinutes = DateDiff("n", StartTime, EndTime) Mod 60
If intMinutes > 0 And intHours > 1 Then
intHours = intHours - 1
End If
End If
' finally, tidy up the result
If intHours >= HOURS_IN_DAY Then ' add to the days
intDays = intDays + (intHours / HOURS_IN_DAY)
intHours = intHours Mod HOURS_IN_DAY
End If
If intMinutes >= 60 Then ' add to the days
intHours = intHours + (intMinutes / 60)
intMinutes = intMinutes Mod 60
End If
'BookedHours = CStr((intDays * HOURS_IN_DAY) + intHours) & " hours, " & CStr(intMinutes) & " minutes"
WorkMinutes = (((intDays * HOURS_IN_DAY) + intHours) * 60) + intMinutes
End Function
Wes
StartDate and StartTime should be one parameter i.e. the StartDate should include the Date and Time .... likewise for the EndDate and EndTime.
I don't know if it should be broken up for the calculation because I don't seem to be getting the correct result.
I could possibly e-mail my little database if anyone is interested to see what's going on with my code.
inimeg:
The function I posted accepts just two arguments: dtmStart As Date, dtmEnd As Date.
"StartDate and StartTime should be one parameter i.e. the StartDate should include the Date and Time .... likewise for the EndDate and EndTime. " You do include the startdate and starttime within the same parameter, i.e. #3/21/2000 8:00:00 AM#
Wes
The function I posted accepts just two arguments: dtmStart As Date, dtmEnd As Date.
"StartDate and StartTime should be one parameter i.e. the StartDate should include the Date and Time .... likewise for the EndDate and EndTime. " You do include the startdate and starttime within the same parameter, i.e. #3/21/2000 8:00:00 AM#
Wes
Wes and Simon
I must allow the calculation to accomodate times outside the valid working time. It is crucial that the time the fault is open reflects the correct number of hours and not include the time that people don't work.
Here's an example ...
Let's say a fault is booked on the system on 17 March 2000 at 09H00.
There is a public holiday on 21 March 2000. Working hours for staff, to fix the problem, is between 08h00 to 16h00 for each day, except Saturday and Sunday and public holidays.
The user draws a report on 22 March 2000 at 14h00 to see how long this fault is open.
The total hours that the fault is open is 21 hours.
Here's how I derived that answer ...
Remember WorkStartTime = 08h00 WorkEndTime = 16h00
From 17/03/2000 09h00 To 17/03/2000 16h00 = 7hrs
The 18 & 19 March 2000 is over weekend so those hours are excluded.
From 20/03/2000 09h00 to 20/03/2000 16h00 = 8hrs
The 21 March 2000 is a public holiday so those hours are excluded.
From 22/03/2000 08h00 to 22/03/2000 14h00 = 6hrs
So number of hours that fault is open = 21 hrs.
Currently the code above does not calculate it in this way, so I can't award any points. Please ... how do I modify the code?
inimeg
Believer, your sample solution is too complexed for what I want to do .. too many parameters :-(
Is there no way it can be simplified to cover what I noted above.
The code provided by simonbennett is sooo close, I'm sure it just has to be adapted accordingly.
As an incentive to come up with code that satisfies my requirements 100% ... I'll increase the points to 200 :-)
inimeg,
I think I have it! After hours of research at my secret Bay location research center I have come up with the followinf function. I believe the problem was down to the fact that it 'cared' about the number of days. Try this one (and don't forget to create a table called BankHoliday containing one field called HolidayDate!)...
Public Function BookedHours(StartTime As String, StartDate As String, EndTime As String, EndDate As String) As String
' constants that indicate the working day
Const WORKING_DAY_START = "08:00"
Const WORKING_DAY_END = "16:00"
' misc temp variables
Dim intMinutes As Integer
Dim intHours As Integer
Dim intDays As Integer
Dim HOURS_IN_DAY As Integer
Dim MINUTES_IN_DAY As Integer
Dim booInd As Boolean
Dim intCount As Integer
Dim intResult As Integer
Dim snpCheck As Recordset
intMinutes = 0
intHours = 0
intDays = 0
HOURS_IN_DAY = DateDiff("h", WORKING_DAY_START, WORKING_DAY_END)
MINUTES_IN_DAY = DateDiff("m", WORKING_DAY_START, WORKING_DAY_END)
' work out the number of days excluding weekends
For intCount = 0 To DateDiff("d", StartDate, EndDate) - 1
intResult = WeekDay(DateAdd("d", intCount, StartDate))
If intResult <> vbSunday And intResult <> vbSaturday Then
Set snpCheck = CurrentDb().OpenRecordset(
If snpCheck.RecordCount = 0 Then
intDays = intDays + 1
End If
snpCheck.Close
End If
Next
intDays = intDays - 1
' work out the number of hours, bearing in mind we alreayd have the number of days
If StartDate <> EndDate Then ' we spread over days
' workout hours on first day
intHours = intHours + DateDiff("h", StartTime, WORKING_DAY_END)
intMinutes = intMinutes + DateDiff("n", StartTime, WORKING_DAY_END) Mod 60
' workout hours on last day
intHours = intHours + DateDiff("h", WORKING_DAY_START, EndTime)
intMinutes = intMinutes + DateDiff("n", WORKING_DAY_START, EndTime) Mod 60
' any days in between?
If intDays >= 1 Then
intHours = intHours + ((intDays) * HOURS_IN_DAY)
'intMinutes = intMinutes + ((intDays) * (MINUTES_IN_DAY Mod 60))
End If
Else ' we are on the same day
intHours = DateDiff("h", StartTime, EndTime)
intMinutes = DateDiff("n", StartTime, EndTime) Mod 60
If intMinutes > 0 And intHours > 1 Then
intHours = intHours - 1
End If
End If
' If intHours >= HOURS_IN_DAY Then ' add to the days
'
' intDays = intDays + (intHours / HOURS_IN_DAY)
' intHours = intHours Mod HOURS_IN_DAY
'
'End If
If intMinutes >= 60 Then ' add to the days
intHours = intHours + (intMinutes / 60)
intMinutes = intMinutes Mod 60
End If
BookedHours = CStr(intHours) & " hours, " & CStr(intMinutes) & " minutes"
End Function
HTH
Simon
I think I have it! After hours of research at my secret Bay location research center I have come up with the followinf function. I believe the problem was down to the fact that it 'cared' about the number of days. Try this one (and don't forget to create a table called BankHoliday containing one field called HolidayDate!)...
Public Function BookedHours(StartTime As String, StartDate As String, EndTime As String, EndDate As String) As String
' constants that indicate the working day
Const WORKING_DAY_START = "08:00"
Const WORKING_DAY_END = "16:00"
' misc temp variables
Dim intMinutes As Integer
Dim intHours As Integer
Dim intDays As Integer
Dim HOURS_IN_DAY As Integer
Dim MINUTES_IN_DAY As Integer
Dim booInd As Boolean
Dim intCount As Integer
Dim intResult As Integer
Dim snpCheck As Recordset
intMinutes = 0
intHours = 0
intDays = 0
HOURS_IN_DAY = DateDiff("h", WORKING_DAY_START, WORKING_DAY_END)
MINUTES_IN_DAY = DateDiff("m", WORKING_DAY_START, WORKING_DAY_END)
' work out the number of days excluding weekends
For intCount = 0 To DateDiff("d", StartDate, EndDate) - 1
intResult = WeekDay(DateAdd("d", intCount, StartDate))
If intResult <> vbSunday And intResult <> vbSaturday Then
Set snpCheck = CurrentDb().OpenRecordset(
If snpCheck.RecordCount = 0 Then
intDays = intDays + 1
End If
snpCheck.Close
End If
Next
intDays = intDays - 1
' work out the number of hours, bearing in mind we alreayd have the number of days
If StartDate <> EndDate Then ' we spread over days
' workout hours on first day
intHours = intHours + DateDiff("h", StartTime, WORKING_DAY_END)
intMinutes = intMinutes + DateDiff("n", StartTime, WORKING_DAY_END) Mod 60
' workout hours on last day
intHours = intHours + DateDiff("h", WORKING_DAY_START, EndTime)
intMinutes = intMinutes + DateDiff("n", WORKING_DAY_START, EndTime) Mod 60
' any days in between?
If intDays >= 1 Then
intHours = intHours + ((intDays) * HOURS_IN_DAY)
'intMinutes = intMinutes + ((intDays) * (MINUTES_IN_DAY Mod 60))
End If
Else ' we are on the same day
intHours = DateDiff("h", StartTime, EndTime)
intMinutes = DateDiff("n", StartTime, EndTime) Mod 60
If intMinutes > 0 And intHours > 1 Then
intHours = intHours - 1
End If
End If
' If intHours >= HOURS_IN_DAY Then ' add to the days
'
' intDays = intDays + (intHours / HOURS_IN_DAY)
' intHours = intHours Mod HOURS_IN_DAY
'
'End If
If intMinutes >= 60 Then ' add to the days
intHours = intHours + (intMinutes / 60)
intMinutes = intMinutes Mod 60
End If
BookedHours = CStr(intHours) & " hours, " & CStr(intMinutes) & " minutes"
End Function
HTH
Simon
SimonBennett
I'm still experiencing hiccups. I evaluated your code with the example I used above. I get 29hrs.
Can't I e-mail my sample database to you to have a look at.
.... you're getting closer with the solution .. I'd say 95% :-)
I'm still experiencing hiccups. I evaluated your code with the example I used above. I get 29hrs.
Can't I e-mail my sample database to you to have a look at.
.... you're getting closer with the solution .. I'd say 95% :-)
Hello again...
When I run the function here from the debug window this is the result...
?bookedhours("09:00","17/0
21 hours, 0 minutes
...honest!
inimeg - can you do me a favour and set a breakpoint on this line:
If snpCheck.RecordCount = 0 Then
Press F8 to step through and you can see if it picks up the date from the table (I have a feeling that the dates are mismatching due to formatting). If it enters the IF and runs the...
intDays = intDays + 1
...line *twice* (for this example), try changing this line:
Set snpCheck = CurrentDb().OpenRecordset(
...to..
Set snpCheck = CurrentDb().OpenRecordset(
...and if that fails, your regoinal setting are different from mine, so try..
Set snpCheck = CurrentDb().OpenRecordset(
Good Luck
Simon
When I run the function here from the debug window this is the result...
?bookedhours("09:00","17/0
21 hours, 0 minutes
...honest!
inimeg - can you do me a favour and set a breakpoint on this line:
If snpCheck.RecordCount = 0 Then
Press F8 to step through and you can see if it picks up the date from the table (I have a feeling that the dates are mismatching due to formatting). If it enters the IF and runs the...
intDays = intDays + 1
...line *twice* (for this example), try changing this line:
Set snpCheck = CurrentDb().OpenRecordset(
...to..
Set snpCheck = CurrentDb().OpenRecordset(
...and if that fails, your regoinal setting are different from mine, so try..
Set snpCheck = CurrentDb().OpenRecordset(
Good Luck
Simon
Simon,
Still no luck .. I've changed the line of code as you suggested with both formats (dd/mm/yyyy and mm/dd/yyyy).
I still get 29hrs instead of 21hrs.
Let me know if I can e-mail my sample to you.
better still (as that is my home email and I can't reply for about 9hrs)post your email so I can send a blank mesage for you to reply to - trust me, my address is a killer....
Simon
Simon
Simon,
Still no luck .. I've changed the line of code as you suggested with both formats (dd/mm/yyyy and mm/dd/yyyy).
I still get 29hrs instead of 21hrs.
Let me know if I can e-mail my sample to you.
I don't have anything yet....
If you have already sent, is the DB zipped? It may not get through our firewall if its too big.
Simon
If you have already sent, is the DB zipped? It may not get through our firewall if its too big.
Simon
Simon
We're having problems with our network again... I'll e-mail it as soon as everything is OK to. I'll post a message to this forum once I've e-mailed it to you. The size of the file is 108KB.
Hi Simon
I've just e-mailed the db to that killer e-mail address of yours.
I'm also going to e-mail it to your home one.
inimeg
I've just e-mailed the db to that killer e-mail address of yours.
I'm also going to e-mail it to your home one.
inimeg
I think I have it! (but I have said that before!)
It's all to do with the holiday table as i suspected. Delete all of the records, then change the format to "dd/mm/yyyy". Also remove your input mask. Now re-enter the records and rerun the code. Success?!?
I will also email this to you.
Simon
It's all to do with the holiday table as i suspected. Delete all of the records, then change the format to "dd/mm/yyyy". Also remove your input mask. Now re-enter the records and rerun the code. Success?!?
I will also email this to you.
Simon
Brilliant ... ! I get the correct answer 21hrs :-)
But... let me throw a spanner in the works :-( Try entering this data
StartDate: 15 March 2000 14h30
EndDate: 17 March 2000 09h30
The correct answer = 11hrs
The program gets 12hrs.
What answer do you get with the sample db I sent?
I'm testing all possible scenarios.
Rgds,
inimeg
Brilliant ... ! I get the correct answer 21hrs :-)
But... let me throw a spanner in the works :-( Try entering this data
StartDate: 15 March 2000 14h30
EndDate: 17 March 2000 09h30
The correct answer = 11hrs
The program gets 12hrs.
What answer do you get with the sample db I sent?
I'm testing all possible scenarios.
Rgds,
inimeg
Okay mate..
This is a new version! What was the problem? Well datediff rounds up! My bad..this version only works in minutes (then calculates the hours), so it should be quicker etc etc - let me know!
Public Function myBookedHours(StartTime As String, StartDate As String, EndTime As String, EndDate As String) As String
' constants that indicate the working day
Const WORKING_DAY_START = "08:00"
Const WORKING_DAY_END = "16:00"
' misc temp variables
Dim intMinutes As Integer
Dim intHours As Integer
Dim intDays As Integer
Dim HOURS_IN_DAY As Integer
Dim MINUTES_IN_DAY As Integer
Dim booInd As Boolean
Dim intCount As Integer
Dim intResult As Integer
Dim snpCheck As Recordset
intMinutes = 0
intHours = 0
intDays = 0
HOURS_IN_DAY = DateDiff("h", WORKING_DAY_START, WORKING_DAY_END)
MINUTES_IN_DAY = DateDiff("n", WORKING_DAY_START, WORKING_DAY_END)
' work out the number of days excluding weekends
For intCount = 0 To DateDiff("d", StartDate, EndDate) - 1
intResult = WeekDay(DateAdd("d", intCount, StartDate))
If intResult <> vbSunday And intResult <> vbSaturday Then
Set snpCheck = CurrentDb().OpenRecordset(
If snpCheck.RecordCount = 0 Then
intDays = intDays + 1
End If
snpCheck.Close
End If
Next
intDays = intDays - 1
' work out the number of hours, bearing in mind we alreayd have the number of days
If StartDate <> EndDate Then ' we spread over days
' workout minutes on first day
intMinutes = intMinutes + DateDiff("n", StartTime, WORKING_DAY_END) 'Mod 60
' workout minutes on last day
intMinutes = intMinutes + DateDiff("n", WORKING_DAY_START, EndTime) 'Mod 60
' any days in between?
If intDays >= 1 Then
intMinutes = intMinutes + ((intDays) * MINUTES_IN_DAY)
End If
Else ' we are on the same day
intMinutes = DateDiff("n", StartTime, EndTime) 'Mod 60
End If
intHours = intHours + (intMinutes / 60)
intMinutes = intMinutes Mod 60
myBookedHours = CStr(intHours) & " hours, " & CStr(intMinutes) & " minutes"
MsgBox "Hours: " & myBookedHours
End Function
Simon
This is a new version! What was the problem? Well datediff rounds up! My bad..this version only works in minutes (then calculates the hours), so it should be quicker etc etc - let me know!
Public Function myBookedHours(StartTime As String, StartDate As String, EndTime As String, EndDate As String) As String
' constants that indicate the working day
Const WORKING_DAY_START = "08:00"
Const WORKING_DAY_END = "16:00"
' misc temp variables
Dim intMinutes As Integer
Dim intHours As Integer
Dim intDays As Integer
Dim HOURS_IN_DAY As Integer
Dim MINUTES_IN_DAY As Integer
Dim booInd As Boolean
Dim intCount As Integer
Dim intResult As Integer
Dim snpCheck As Recordset
intMinutes = 0
intHours = 0
intDays = 0
HOURS_IN_DAY = DateDiff("h", WORKING_DAY_START, WORKING_DAY_END)
MINUTES_IN_DAY = DateDiff("n", WORKING_DAY_START, WORKING_DAY_END)
' work out the number of days excluding weekends
For intCount = 0 To DateDiff("d", StartDate, EndDate) - 1
intResult = WeekDay(DateAdd("d", intCount, StartDate))
If intResult <> vbSunday And intResult <> vbSaturday Then
Set snpCheck = CurrentDb().OpenRecordset(
If snpCheck.RecordCount = 0 Then
intDays = intDays + 1
End If
snpCheck.Close
End If
Next
intDays = intDays - 1
' work out the number of hours, bearing in mind we alreayd have the number of days
If StartDate <> EndDate Then ' we spread over days
' workout minutes on first day
intMinutes = intMinutes + DateDiff("n", StartTime, WORKING_DAY_END) 'Mod 60
' workout minutes on last day
intMinutes = intMinutes + DateDiff("n", WORKING_DAY_START, EndTime) 'Mod 60
' any days in between?
If intDays >= 1 Then
intMinutes = intMinutes + ((intDays) * MINUTES_IN_DAY)
End If
Else ' we are on the same day
intMinutes = DateDiff("n", StartTime, EndTime) 'Mod 60
End If
intHours = intHours + (intMinutes / 60)
intMinutes = intMinutes Mod 60
myBookedHours = CStr(intHours) & " hours, " & CStr(intMinutes) & " minutes"
MsgBox "Hours: " & myBookedHours
End Function
Simon
Hi Simon :-)
This is excellent thanks. It works perfectly!!
Your code satisfies my requirements 100%. Thanks for your hard work.
I will award the points to you ... just one last problem.
What if during the work day, 08h00 - 16h00, there is a lunch of 30 minutes?
Would it be better just to subtract the 30 minutes from my final answer HrsBooked. Is there an easier option?
Thanks again,
inimeg
Wait ....
Try this sample data
StartDate = 15 March 2000 09h23
EndDate = 15 March 2000 14h01
Correct Answer = 4hrs 38 min
Program gets 5hrs 38 min
Text from my email to ya...
inimeg,
Guess what? The site appears to be down again, so, I thought I would let you know the good news (it went down as I was sending a comment)
Change the last lines of the function to
intHours = intHours + Int((intMinutes / 60)) ' <<<< THIS IS THE AMMENDED LINE
intMinutes = intMinutes Mod 60
myBookedHours = CStr(intHours) & " hours, " & CStr(intMinutes) & " minutes"
MsgBox "Hours: " & myBookedHours
End Function
We need to use INT() here to make access round down, in your last example the result (i.e. total minutes / 60) was 278 minutes, or 4.63 hours. This wass rounded up to 5 and then the modulus was added. So using int ensures rounding down and removes the 'doubling up' of the modulus.
Let me know how it goes.
Simon
inimeg,
Guess what? The site appears to be down again, so, I thought I would let you know the good news (it went down as I was sending a comment)
Change the last lines of the function to
intHours = intHours + Int((intMinutes / 60)) ' <<<< THIS IS THE AMMENDED LINE
intMinutes = intMinutes Mod 60
myBookedHours = CStr(intHours) & " hours, " & CStr(intMinutes) & " minutes"
MsgBox "Hours: " & myBookedHours
End Function
We need to use INT() here to make access round down, in your last example the result (i.e. total minutes / 60) was 278 minutes, or 4.63 hours. This wass rounded up to 5 and then the modulus was added. So using int ensures rounding down and removes the 'doubling up' of the modulus.
Let me know how it goes.
Simon
BTW - as for lunch, it may not be as easy as it seems. We need to take into account when lunch is - e.g. if the problem is 08:00 to 12:00 do we remove the half hour? Also, if we span multiple days we will need to remove the lunch period for each day, which simply means:
intMinutes = intMinutes - (30 * intDays)
Let me have a think....
intMinutes = intMinutes - (30 * intDays)
Let me have a think....
...results.
Unfortunately it seems that you have to have a uniform lunch time for each day - set the
Const LUNCH_PERIOD_START = "12:00"
Const LUNCH_PERIOD_DURATION = 30
constants to change this, or add them as parameters to the function, so you can set them up when the code is called.
Public Function myBookedHours(StartTime As String, StartDate As String, EndTime As String, EndDate As String) As String
' constants that indicate the working day
Const WORKING_DAY_START = "08:00"
Const WORKING_DAY_END = "16:00"
Const LUNCH_PERIOD_START = "12:00"
Const LUNCH_PERIOD_DURATION = 30
' misc temp variables
Dim intMinutes As Integer
Dim intHours As Integer
Dim intDays As Integer
Dim HOURS_IN_DAY As Integer
Dim MINUTES_IN_DAY As Integer
Dim booInd As Boolean
Dim intCount As Integer
Dim intResult As Integer
Dim snpCheck As Recordset
intMinutes = 0
intHours = 0
intDays = 0
HOURS_IN_DAY = DateDiff("h", WORKING_DAY_START, WORKING_DAY_END)
MINUTES_IN_DAY = DateDiff("n", WORKING_DAY_START, WORKING_DAY_END)
' work out the number of days excluding weekends
For intCount = 0 To DateDiff("d", StartDate, EndDate) - 1
intResult = WeekDay(DateAdd("d", intCount, StartDate))
If intResult <> vbSunday And intResult <> vbSaturday Then
Set snpCheck = CurrentDb().OpenRecordset(
If snpCheck.RecordCount = 0 Then
intDays = intDays + 1
End If
snpCheck.Close
End If
Next
intDays = intDays - 1
' work out the number of hours, bearing in mind we alreayd have the number of days
If StartDate <> EndDate Then ' we spread over days
' workout minutes on first day
intMinutes = intMinutes + DateDiff("n", StartTime, WORKING_DAY_END) 'Mod 60
If StartTime <= LUNCH_PERIOD_START Then ' do we start before lunch?
intMinutes = intMinutes - LUNCH_PERIOD_DURATION
End If
' workout minutes on last day
intMinutes = intMinutes + DateDiff("n", WORKING_DAY_START, EndTime) 'Mod 60
If EndTime >= LUNCH_PERIOD_START Then ' finished after lunch
intMinutes = intMinutes - LUNCH_PERIOD_DURATION
End If
' any days in between?
If intDays >= 1 Then
intMinutes = intMinutes + ((intDays) * MINUTES_IN_DAY)
intMinutes = intMinutes - LUNCH_PERIOD_DURATION ' remove lunch for each whole day
End If
Else ' we are on the same day
intMinutes = DateDiff("n", StartTime, EndTime) 'Mod 60
If StartTime <= LUNCH_PERIOD_START Then ' are we before lunch?
intMinutes = intMinutes - LUNCH_PERIOD_DURATION
End If
End If
intHours = intHours + Int((intMinutes / 60))
intMinutes = intMinutes Mod 60
myBookedHours = CStr(intHours) & " hours, " & CStr(intMinutes) & " minutes"
MsgBox "Hours: " & myBookedHours
End Function
HTDI!
Simon
Unfortunately it seems that you have to have a uniform lunch time for each day - set the
Const LUNCH_PERIOD_START = "12:00"
Const LUNCH_PERIOD_DURATION = 30
constants to change this, or add them as parameters to the function, so you can set them up when the code is called.
Public Function myBookedHours(StartTime As String, StartDate As String, EndTime As String, EndDate As String) As String
' constants that indicate the working day
Const WORKING_DAY_START = "08:00"
Const WORKING_DAY_END = "16:00"
Const LUNCH_PERIOD_START = "12:00"
Const LUNCH_PERIOD_DURATION = 30
' misc temp variables
Dim intMinutes As Integer
Dim intHours As Integer
Dim intDays As Integer
Dim HOURS_IN_DAY As Integer
Dim MINUTES_IN_DAY As Integer
Dim booInd As Boolean
Dim intCount As Integer
Dim intResult As Integer
Dim snpCheck As Recordset
intMinutes = 0
intHours = 0
intDays = 0
HOURS_IN_DAY = DateDiff("h", WORKING_DAY_START, WORKING_DAY_END)
MINUTES_IN_DAY = DateDiff("n", WORKING_DAY_START, WORKING_DAY_END)
' work out the number of days excluding weekends
For intCount = 0 To DateDiff("d", StartDate, EndDate) - 1
intResult = WeekDay(DateAdd("d", intCount, StartDate))
If intResult <> vbSunday And intResult <> vbSaturday Then
Set snpCheck = CurrentDb().OpenRecordset(
If snpCheck.RecordCount = 0 Then
intDays = intDays + 1
End If
snpCheck.Close
End If
Next
intDays = intDays - 1
' work out the number of hours, bearing in mind we alreayd have the number of days
If StartDate <> EndDate Then ' we spread over days
' workout minutes on first day
intMinutes = intMinutes + DateDiff("n", StartTime, WORKING_DAY_END) 'Mod 60
If StartTime <= LUNCH_PERIOD_START Then ' do we start before lunch?
intMinutes = intMinutes - LUNCH_PERIOD_DURATION
End If
' workout minutes on last day
intMinutes = intMinutes + DateDiff("n", WORKING_DAY_START, EndTime) 'Mod 60
If EndTime >= LUNCH_PERIOD_START Then ' finished after lunch
intMinutes = intMinutes - LUNCH_PERIOD_DURATION
End If
' any days in between?
If intDays >= 1 Then
intMinutes = intMinutes + ((intDays) * MINUTES_IN_DAY)
intMinutes = intMinutes - LUNCH_PERIOD_DURATION ' remove lunch for each whole day
End If
Else ' we are on the same day
intMinutes = DateDiff("n", StartTime, EndTime) 'Mod 60
If StartTime <= LUNCH_PERIOD_START Then ' are we before lunch?
intMinutes = intMinutes - LUNCH_PERIOD_DURATION
End If
End If
intHours = intHours + Int((intMinutes / 60))
intMinutes = intMinutes Mod 60
myBookedHours = CStr(intHours) & " hours, " & CStr(intMinutes) & " minutes"
MsgBox "Hours: " & myBookedHours
End Function
HTDI!
Simon
Simon .... you're a star :-)
Everything works 100%.
Thanks for your time and hard work.
I really appreciate it.
Have a good day.
Regards,
inimeg
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
Featured Post
There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.
Tackle projects and never again get stuck behind a technical roadblock.
Join Now
Public Function BookedHours(StartTime As String, StartDate As String, EndTime As String, EndDate As String) As Long
' constants that indicate the working day
Const WORKING_DAY_START = "08:00"
Const WORKING_DAY_END = "16:00"
' misc temp variables
Dim intMinutes As Integer
Dim intHours As Integer
Dim intDays As Integer
Dim HOURS_IN_DAY As Integer
Dim MINUTES_IN_DAY As Integer
Dim booInd As Boolean
Dim intCount As Integer
Dim intResult As Integer
Dim snpCheck as recordset
intMinutes = 0
intHours = 0
intDays = 0
HOURS_IN_DAY = DateDiff("h", WORKING_DAY_START, WORKING_DAY_END)
MINUTES_IN_DAY = DateDiff("m", WORKING_DAY_START, WORKING_DAY_END)
' work out the number of days excluding weekends
For intCount = 0 To DateDiff("d", StartDate, EndDate) - 1
intResult = WeekDay(DateAdd("d", intCount, StartDate))
If intResult <> vbSunday And intResult <> vbSaturday Then
set snpCheck = currentdb().openrecordset(
if snpcheck.recordcount = 0 then
intDays = intDays + 1
end if
snpcheck.close
End If
Next
intDays = intDays - 1
' work out the number of hours, bearing in mind we alreayd have the number of days
If StartDate <> EndDate Then ' we spread over days
' workout hours on first day
intHours = intHours + DateDiff("h", StartTime, WORKING_DAY_END)
intMinutes = intMinutes + DateDiff("n", StartTime, WORKING_DAY_END) Mod 60
' workout hours on last day
intHours = intHours + DateDiff("h", WORKING_DAY_START, EndTime)
intMinutes = intMinutes + DateDiff("n", WORKING_DAY_START, EndTime) Mod 60
' any days in between?
If intDays > 2 Then
intHours = intHours + ((intDays - 1) * HOURS_IN_DAY)
intMinutes = intMinutes + ((intDays - 1) * (MINUTES_IN_DAY Mod 60))
End If
Else ' we are on the same day
intHours = DateDiff("h", StartTime, EndTime)
intMinutes = DateDiff("n", StartTime, EndTime) Mod 60
If intMinutes > 0 And intHours > 1 Then
intHours = intHours - 1
End If
End If
' finally, tidy up the result
If intHours >= HOURS_IN_DAY Then ' add to the days
intDays = intDays + (intHours / HOURS_IN_DAY)
intHours = intHours Mod HOURS_IN_DAY
End If
If intMinutes >= 60 Then ' add to the days
intHours = intHours + (intMinutes / 60)
intMinutes = intMinutes Mod 60
End If
BookedHours = CStr((intDays*HOURS_IN_DAY
End Function
HTH
Simon