By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,197 Members | 1,210 Online
Bytes IT Community
+ Ask a Question
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
Share this Question
Share on Google+
14 Replies


NeoPa
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

NeoPa
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

NeoPa
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
  1. Public Function WorkHours(datStart As Date, datEnd As Date) As Date
  2.     Dim lngDays As Long, lngStartDay As Long, lngEndDay As Long
  3.     Dim datStartTime As Date, datEndTime As Date, datTime As Date
  4.  
  5.     'Dates stored as integral days with the time as a fractional portion
  6.     lngStartDay = CLng(Left(Format(datStart, "0000000000.0000000000"), 10))
  7.     lngEndDay = CLng(Left(Format(datEnd, "0000000000.0000000000"), 10))
  8.     datStartTime = datStart - lngStartDay
  9.     datEndTime = datEnd - lngEndDay
  10.     If datStartTime < #8:30:00 AM# Then datStartTime = #8:30:00 AM#
  11.     If datStartTime > #5:00:00 PM# Then
  12.         lngStartDay = lngStartDay + 1
  13.         datStartTime = #8:30:00 AM#
  14.     End If
  15.     If datEndTime > #5:00:00 PM# Then datEndTime = #5:00:00 PM#
  16.     If datEndTime < #8:30:00 AM# Then
  17.         lngEndDay = lngEndDay - 1
  18.         datEndTime = #5:00:00 PM#
  19.     End If
  20.     If (CInt(Format(datStart, "w")) Mod 7) < 2 Then
  21.         lngStartDay = lngStartDay + 3 - CLng(Format(datStart, "w"))
  22.         datStartTime = #8:30:00 AM#
  23.     End If
  24.     If (CInt(Format(datEnd, "w")) Mod 7) < 2 Then
  25.         lngEndDay = lngEndDay - CLng(Format(datEnd, "w"))
  26.         datEndTime = #5:00:00 PM#
  27.     End If
  28.     datStart = lngStartDay + datStartTime
  29.     datEnd = lngEndDay + datEndTime
  30.     WorkHours = 0
  31.     If (lngEndDay - lngStartDay) > 7 Then
  32.         lngDays = ((lngEndDay - lngStartDay) \ 7) * 5
  33.         WorkHours = WorkHours + lngDays
  34.         lngEndDay = lngEndDay - ((lngDays / 5) * 7)
  35.     End If
  36.     If Format(lngEndDay, "ww") > Format(lngStartDay, "ww") Then
  37.         WorkHours = WorkHours + 5
  38.         lngEndDay = lngEndDay - 7
  39.     End If
  40.     datTime = datEndTime - datStartTime
  41.     If datEndTime < datStartTime Then
  42.         datTime = datTime + #8:30:00 AM#
  43.         WorkHours = WorkHours - 1
  44.     End If
  45.     WorkHours = WorkHours + _
  46.                 (CInt(Format(lngEndDay, "w")) Mod 7) - _
  47.                 (CInt(Format(lngStartDay, "w")) Mod 7) + _
  48.                 datTime
  49. End Function
Oct 28 '06 #6

Andrew Thackray
P: 76
Another approach is to use a loop.

Expand|Select|Wrap|Line Numbers
  1.  
  2. dim NoOfDays as long
  3. Dim NextDay as date
  4. Dim hrs as single
  5.  
  6.          NextDay = StartDate
  7.  
  8. AddADay:
  9.  
  10.          hrs = hrs * 8.5
  11.  
  12.  
Oct 28 '06 #7

Andrew Thackray
P: 76
Another approach is to use a loop.

Expand|Select|Wrap|Line Numbers
  1.  
  2. dim NoOfDays as long
  3. Dim NextDay as date
  4. Dim hrs as single
  5.  
  6.          NextDay = StartDate
  7.  
  8. AddADay:
  9.  
  10.          if weekday(nextday) > 1 and weekday(nextday) < 7 then hrs = hrs * 8.5
  11.          nextday = dateadd("d",1,nextday)
  12.          if nextday <= enddate then goto AddADay
  13.  
  14.  
  15.  
Oct 28 '06 #8

NeoPa
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

MMcCarthy
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

NeoPa
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

NeoPa
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

Post your reply

Sign in to post your reply or Sign up for a free account.