working hours between two times

[niknokio]

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

[NeoPa]

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?

[niknokio]

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

[NeoPa]

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.

[niknokio]

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

[NeoPa]

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?

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

[Andrew Thackray]

Another approach is to use a loop.

 
dim NoOfDays as long
Dim NextDay as date
Dim hrs as single
 
         NextDay = StartDate
 
AddADay:
 
         hrs = hrs * 8.5
 
 

[Andrew Thackray]

Another approach is to use a loop.

 
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
 
 
 

[NeoPa]

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...

[MMcCarthy]

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

[NeoPa]

Thanks M.

Int() and Fix().
I confused CInt() with Int() when I was looking.

[niknokio]

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

[iceone]

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)

[NeoPa]

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).

[youmike]

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.