Is calculating BUSINESS HOURS possible...? 1

[THEGREEK]

I've been perplexed by this idea for the longest time now, I was wondering if anyone can offer any kind of advice of going about calculating the following:

I have two date fields that i want measured, both fields have the date as well as the time included, now I need to measure from '1/31/2001 04:30:00 PM' to '2/1/2001 11:30:00 AM', now if I measure this using the traditional business days method it gives me one day, I defenitely don't want that, what I do want though is, (keeping in mind that my company's reg bus hours are 8 am to 5 pm) (ie. I don't want to be penalized by saying it took 19hrs, I want it to say from 1/31/2001 04:30:00 PM to 2/1/2001 11:30:00 AM only really took 2hrs (because the day will start only from 8:00 am to 5:00 pm)

I've probably confused everyone here, including myself

Any advice would be greatly appreciated,

GReeKGoD

 

[MichaelRed]

Hmmmmmmmmmmmmm,

Assuming we can agree on the basic arithmatic, it doesn;t look all that difficult.

But first things first. My time calculator says it is FOUR hours (in the normal workday) from 2/1/01 4:30P to 2/2/01 11:30A. Can we agree on that? If not please explain.

Otherwise, it seems that the calculation is Straightforward, until we need to discuss OVERTIME.

There is a "workdays" function whic will return the number of "Whole" working days between two dates. Assuming that the first and last days are the only partial days on the task, then 8 * Min(workdays -1), 0) should return the hous for all of the whole days. To this we can just add the first day hours and the last day hours. The two "functions" below provide these hours. So, there, it's done.

Now - about the overtime. You can't do it in this format. It does/will occur. Even if the company doesn't pay for it, if you'r ever going to have accurate reporting you need to know what the REAL time it takes to do something.

So, the above is 'correct' in the academic sense that it is what you requested. It is totally bougs in the real world.

Public Function StrtDayHrs(StartTime As Date) As Single

    Dim EndTime As Date

    EndTime = DateAdd("n", 60 * 17, Format(StartTime, "Short Date"))   'Time(Start)

    StrtDayHrs = (DateDiff("n", StartTime, EndTime) / 60)

End Function

Public Function EndDayHrs(EndTime As Date) As Single

    Dim StartTime As Date

    StartTime = DateAdd("n", 60 * 8, Format(EndTime, "Short Date"))

    EndDayHrs = (DateDiff("n", StartTime, EndTime) / 60)

End Function
[code]
 

MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[THEGREEK]

hi Michael,

thanx alot for the prompt response you helped me in a great deal, much appreciated...

I was just wondering what did you mean by 8 * Min(workdays -1), 0) ...when i use this expression access gives me an error, am I forgetting something?

Please advise

thanx again for all your help

THEGREEK

 

[THEGREEK]

btw, michael

I hope i'm not asking for too much, but do you have a working sample of the above in a db that I can have a look at maybe?

much appreciated,

THEGREEK

 

[MichaelRed]

[blue]There is a "workdays" function whic will return the number of "Whole" working days between two dates.[/blue]

mea culpa, mea culpa.


This should have been 'explained' a bit. There is one (or more) FAQ articles on the calculation of "Working days". I really only meant to refer to these articles. You need to look up that calculation. Since it already exists, I did not wnat to repeat it here, but just refer you to it. The two "functions" posted are (obviously?) just to get the hours for the first and last date of work.


The Expression
by 8 * Min(workdays -1), 0) was only intended as a "guide" in more or less algebraic expression syntax to refer to the previously noted FAQ article and funtion found there in, and place the use in context. Actually, you need to do your own "Min", as this is not part of Ms. Access VBA, however I thought it to trivial to bother w/ (Wrong again!)


The Warapper for the whole enchallidia

Public Function basWorkHours(StrtDt As Date, EndDt As Date) As Single

    'Calculate Working Hours between [StrtDt] and [EndDt]

    Dim WrkHrs As Single

    WrkHrs = 8 * (basDeltaDays(StrtDt, EndDt) - 1)  'Eight Hrs Per FULL day
    If (WrkHrs < 0) Then                            'No Negative Workdays allowed
        WrkHrs = 0                                  'The &quot;Min&quot; Function
    End If

    WrkHrs = WrkHrs + StrtDayHrs(StrtDt)            'Previously Posted Routines
    WrkHrs = WrkHrs + EndDayHrs(EndDt)

    basWorkHours = WrkHrs                           'My FINAL Answer _
                                                    (Even if it isn't worth a $$$$M)

End Function

The plain vanillia working days thingggggy

Public Function basDeltaDays(StartDate As Date, EndDate As Date) As Integer
'HoliDate   HoliName
'1/1/00     New Year's Day
'1/17/00    Martin Luther King Day
'2/2/00     Groundhog Day
'2/12/00    Lincon's Birthday
'2/14/00    Valentine's Day
'2/21/00    President's Day
'2/22/00    Washington's Birthday
'3/8/00     Ash Wednesday
'3/17/00    St. Patrick's Day
'4/1/00     April Fool's Day
'4/20/00    Passover
'4/21/00    Good Friday
'5/5/00     Cinco de Mayo
'5/14/00    Mother's Day
'6/11/00    Pentecost
'6/18/00    Father's Day
'7/4/00     Independence Day
'9/4/00     Labor Day
'10/31/00   Halloween
'11/11/00   Vetran's Day
'11/23/00   Thanksgiving
'12/25/00   Christmas
'12/31/00   New Year's Eve

    'Get the number of workdays between the given dates

    Dim dbs As Database
    Dim rstHolidays As Recordset

    Dim Idx As Long
    Dim MyDate As Date
    Dim FirstHoliday As Date
    Dim LastHoliday As Date
    Dim NumDays As Long
    Dim strCriteria As String
    Dim NumSgn As String * 1

    Set dbs = CurrentDb
    Set rstHolidays = dbs.OpenRecordset("tblHolidays", dbOpenDynaset)

    NumSgn = Chr(35)
    FirstHoliday = rstHolidays!Holidate
    rstHolidays.MoveLast
    LastHoliday = rstHolidays!Holidate

    MyDate = Format(StartDate, "Short Date")
    If (MyDate > LastHoliday Or MyDate < FirstHoliday) Then
        Exit Function
    End If

    For Idx = CLng(StartDate) To CLng(EndDate) Step Sgn(EndDate - StartDate)
        Select Case (WeekDay(MyDate))
            Case Is = 1     'Sunday
                'Do Nothing, it is NOT a Workday

            Case Is = 7     'Saturday
                'Do Nothing, it is NOT a Workday

            Case Else       'Normal Workday
                strCriteria = &quot;[HoliDate] = &quot; &amp; NumSgn &amp; MyDate &amp; NumSgn
                rstHolidays.FindFirst strCriteria
                If (rstHolidays.NoMatch) Then
                    NumDays = NumDays + 1
                 Else
                    'Do Nothing, it is NOT a Workday
                End If

        End Select

        MyDate = DateAdd(&quot;d&quot;, Sgn(EndDate - StartDate), MyDate)
        If (MyDate > LastHoliday Or MyDate < FirstHoliday) Then
            Exit Function
        End If

    Next Idx

    basDeltaDays = NumDays

End Function

AGAIN, the hours for the starting day

Public Function StrtDayHrs(StartTime As Date) As Single

    Dim EndTime As Date

    EndTime = DateAdd("n", 60 * 17, Format(StartTime, "Short Date"))   'Time(Start)

    StrtDayHrs = (DateDiff("n", StartTime, EndTime) / 60)

End Function

And Finally, the thinnggyyyyy for the hours on the last day

Public Function EndDayHrs(EndTime As Date) As Single

    Dim StartTime As Date

    StartTime = DateAdd("n", 60 * 8, Format(EndTime, "Short Date"))

    EndDayHrs = (DateDiff("n", StartTime, EndTime) / 60)

End Function

MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[sdk]

Hi,

After spending a fair bit of effort trying to get the right number of days up, only to read MRs reponse that that function was already available (doh!), I had already finished the function below. Will add that this is a bit flexible in the number of hours per day, and the lunch period. Didn't include pub hols, though this could be fairly easily added through a dcount of all (weekday) dates within the work period.

It does have a tendancy to be a bit precise, but prob a problem no matter the method used.

Is a bit(!) proceedural, but what they hec:

Public Function HoursDone(datWorkStart As Date, datWorkFinish As Date) As Double
Dim intWeekEnds As Integer
Dim intDays As Integer
Dim dblStartTime As Double
Dim dblFinishTime As Double
Dim lngEndDay As Long
Dim lngStartDay As Long
Dim dblLunchStart As Double
Dim dblLunchFinish As Double

'check dates are valid, not weekends and start before finish
If datWorkStart <= datWorkFinish And DatePart(&quot;w&quot;, datWorkStart, vbMonday) < 6 And DatePart(&quot;w&quot;, datWorkFinish, vbMonday) < 6 Then

'set the int value for the start &amp; finish full working days
lngStartDay = Int(datWorkStart)
lngEndDay = Int(datWorkFinish)

'set variables for start/finish/lunch times
dblStartTime = (8 / 24) '8am
dblFinishTime = (17 / 24) '5pm
dblLunchStart = (12 / 24) '12pm
dblLunchFinish = (13 / 24) '1pm

'determine number of weekends
intWeekEnds = Int((lngEndDay - lngStartDay) / 7)
'find out if extra weekend spanned
If DatePart(&quot;w&quot;, datWorkStart, vbSunday) > DatePart(&quot;w&quot;, datWorkFinish, vbSunday) Then
intWeekEnds = intWeekEnds + 1
End If

'number of worksdays
intDays = ((lngEndDay + 1) - lngStartDay) - intWeekEnds * 2

'estimated hours worked
HoursDone = intDays * (((dblFinishTime - dblStartTime) - (dblLunchFinish - dblLunchStart)) * 24)
'add the start and finish hours
HoursDone = HoursDone - (((Int(datWorkFinish) + dblFinishTime) - datWorkFinish) * 24)
HoursDone = HoursDone - ((datWorkStart - (lngStartDay + dblStartTime)) * 24)

'adjust for lunch period/s
If (datWorkStart > (lngStartDay + dblLunchFinish) And datWorkFinish > (lngEndDay + dblLunchFinish)) Or (datWorkStart < (lngStartDay + dblLunchFinish) And datWorkFinish < (lngEndDay + dblLunchFinish)) Then
HoursDone = HoursDone + 1 * (dblLunchFinish - dblLunchStart) * 24
ElseIf datWorkStart > (lngStartDay + dblLunchFinish) And datWorkFinish < (lngEndDay + dblLunchFinish) Then
HoursDone = HoursDone + 2 * (dblLunchFinish - dblLunchStart) * 24
End If

'make it neat
HoursDone = HoursDone * 100 'multiply result by 100
HoursDone = Int(HoursDone) / 100 'to make to 2 dec places

Else
'do something else
End If

End Function