435,197 Members | 1,210 Online
Need help? Post your question and get tips & solutions from a community of 435,197 IT Pros & Developers. It's quick & easy.

# working hours between two times

 P: 4 Hi I need to be able to calculate the working time between a StartDate and an EndDate. the data is in dd/mm/yyyy hh:mm:ss format and given an 8.5 hr day (weekdays). Ive found lots of really useful stuff on this list but nothing that really does it for me. I've have used a bit of code that lets me know the days Public Function GetNumberOfWorkDays(sStartDate, sEndDate) Dim iDays Dim iWorkDays Dim sDay Dim i iDays = DateDiff("d", sStartDate, sEndDate) iWorkDays = 0 For i = 0 To iDays 'First day of the week is sunday sDay = Weekday(DateAdd("d", i, sStartDate)) If sDay <> 1 And sDay <> 7 Then iWorkDays = iWorkDays + 1 End If Next GetNumberOfWorkDays = iWorkDays End Function (thanks again to you guys) but i think that i need to think about this radically different (and beyond my knowledge) to get what i need. Has anyone come across anything like this before and if so would you be able to help me out (and more to the point annoy my manager who dropped this on me in the first place) I have to confess that its been 6 years since i last used access in anger please forgive me. Nick Oct 27 '06 #1
14 Replies

 Expert Mod 15k+ P: 31,494 Do you have some example data? Can you express precisely what you're after? In the form :- Working hours are from 09:00 to 13:00 and from 14:00 to 18:30 on weekdays. I need to determine, given two date/times, how much working time has elapsed between them. This is just my guess, of course. Is your requirement similar to this? Oct 27 '06 #2

 P: 4 Do you have some example data? Can you express precisely what you're after? In the form :- Working hours are from 09:00 to 13:00 and from 14:00 to 18:30 on weekdays. I need to determine, given two date/times, how much working time has elapsed between them. This is just my guess, of course. Is your requirement similar to this? No worries and thanks for responding working hours are between 08:30 to 17:00 - no breaks - its a call centre :-) yes the requirement is similar. I'm looking at call data so if StartDate = 11/09/2006 09:30:00 & EndDate is 26/09/2006 11:30:15 there are 11 days 2 hours and 15 seconds of working time between opening and closing calls. hope this makes it clearer Nick Oct 27 '06 #3

 Expert Mod 15k+ P: 31,494 Crystal! Unfortunately the problem itself is quite involved. I'll look at producing something for you but I'm at work ATM and something of this size will take some time. Oct 27 '06 #4

 P: 4 Anything you can do would be much appreciated thank you. There is no major panic as i'm being sent off on another adventure for the next 10 days anyway so at least i get some breathing space :-) Thanks again for responding and in advance for any help you can offer. Nick Oct 27 '06 #5

 Expert Mod 15k+ P: 31,494 This doesn't seem to work for all possible combinations of dates but I think it's close. BTW does anyone know a way of extracting the integer part of a number without rounding it in Access VBA? Expand|Select|Wrap|Line Numbers Public Function WorkHours(datStart As Date, datEnd As Date) As Date     Dim lngDays As Long, lngStartDay As Long, lngEndDay As Long     Dim datStartTime As Date, datEndTime As Date, datTime As Date       'Dates stored as integral days with the time as a fractional portion     lngStartDay = CLng(Left(Format(datStart, "0000000000.0000000000"), 10))     lngEndDay = CLng(Left(Format(datEnd, "0000000000.0000000000"), 10))     datStartTime = datStart - lngStartDay     datEndTime = datEnd - lngEndDay     If datStartTime < #8:30:00 AM# Then datStartTime = #8:30:00 AM#     If datStartTime > #5:00:00 PM# Then         lngStartDay = lngStartDay + 1         datStartTime = #8:30:00 AM#     End If     If datEndTime > #5:00:00 PM# Then datEndTime = #5:00:00 PM#     If datEndTime < #8:30:00 AM# Then         lngEndDay = lngEndDay - 1         datEndTime = #5:00:00 PM#     End If     If (CInt(Format(datStart, "w")) Mod 7) < 2 Then         lngStartDay = lngStartDay + 3 - CLng(Format(datStart, "w"))         datStartTime = #8:30:00 AM#     End If     If (CInt(Format(datEnd, "w")) Mod 7) < 2 Then         lngEndDay = lngEndDay - CLng(Format(datEnd, "w"))         datEndTime = #5:00:00 PM#     End If     datStart = lngStartDay + datStartTime     datEnd = lngEndDay + datEndTime     WorkHours = 0     If (lngEndDay - lngStartDay) > 7 Then         lngDays = ((lngEndDay - lngStartDay) \ 7) * 5         WorkHours = WorkHours + lngDays         lngEndDay = lngEndDay - ((lngDays / 5) * 7)     End If     If Format(lngEndDay, "ww") > Format(lngStartDay, "ww") Then         WorkHours = WorkHours + 5         lngEndDay = lngEndDay - 7     End If     datTime = datEndTime - datStartTime     If datEndTime < datStartTime Then         datTime = datTime + #8:30:00 AM#         WorkHours = WorkHours - 1     End If     WorkHours = WorkHours + _                 (CInt(Format(lngEndDay, "w")) Mod 7) - _                 (CInt(Format(lngStartDay, "w")) Mod 7) + _                 datTime End Function Oct 28 '06 #6

 P: 76 Another approach is to use a loop. Expand|Select|Wrap|Line Numbers   dim NoOfDays as long Dim NextDay as date Dim hrs as single            NextDay = StartDate   AddADay:            hrs = hrs * 8.5     Oct 28 '06 #7

 P: 76 Another approach is to use a loop. Expand|Select|Wrap|Line Numbers   dim NoOfDays as long Dim NextDay as date Dim hrs as single            NextDay = StartDate   AddADay:            if weekday(nextday) > 1 and weekday(nextday) < 7 then hrs = hrs * 8.5          nextday = dateadd("d",1,nextday)          if nextday <= enddate then goto AddADay       Oct 28 '06 #8

 Expert Mod 15k+ P: 31,494 I admit that I sometimes I don't see the wood for the trees. Normally going straight for an answer arithmetically is my preferred approach rather than looping etc (probably because when I learned my trade instructions executed at a rate of some hundreds per second - don't think I'm exaggerating). In this case, with most data resulting in only a few iterations anyway and processors running at some billions per second (for simpler instructions at least) this is absolutely not an issue. The fact that I goofed up the code somewhere too doesn't help :-(. Let me see if I can redeem myself a little then :- As the Weekday() function is used (another thing I missed) you can use the Weekday([DateField],vbSaturday) version to start the week on Saturday, which makes testing for a weekend date a little easier. I shall now go off and kick myself for a while for wasting so much time on the earlier heap of... Oct 28 '06 #9

 Expert Mod 10K+ P: 14,534 BTW does anyone know a way of extracting the integer part of a number without rounding it in Access VBA? Fix(24.78) will return 24 Oct 28 '06 #10

 Expert Mod 15k+ P: 31,494 Thanks M. Int() and Fix(). I confused CInt() with Int() when I was looking. Oct 28 '06 #11

 P: 4 Many thanks to all you guys. Have just returned from my managers last whim to see that you have given me loads of things to try. great to see that there are people out there who want to help. I do hope that i can contribute something back to the board in the future. Nik Nov 10 '06 #12

 P: 2 Hi everybody, I am monitoring a call center and i need to calculate the working hours between two times. I just started with access as the dataset has become too big to be handled in ecxel. i managed to calculate the working day difference (ie excluding sundays, saturdays, thanksgiving, ecc,) but i'm stuck with the hours... have u already built a module for this purpose? thank u all PS i consider as working hours from 09;00 am to 19:30 pm (monday-fryday) Jul 4 '08 #13

 Expert Mod 15k+ P: 31,494 You need to post your question in a new thread if you want help. PS. You may link between the threads if you like (either way or even both). Jul 7 '08 #14

 P: 69 This thread asks an almost identical question to another I've just commented on. Here's my view: Basically, there is really no reliable alternative to purpose built code which takes start date & time, end date & time and checks each successive date for day of the week and then in a table which lists the holidays for your particular location. It's not an uncommon need, but because different countries have different holidays, some of which (Easter, for instance - different in Greece from Italy) change actual date from year to year, I doubt that an of the shelf global solution exists. Sorry to be so negative. Jul 8 '08 #15