Hours - Weekday count module 1

[Noah114]

Does anyone know a way, or have a module that can be used in Access, where the calculation between a start date and end date are calculated in hours, and does not count weekends? In other words, a start date on Friday a 6:00pm and and end date of Monday 9:00am would show "15" as the result?

 

[dhookom]

Try this

Public Function WeekdayHours(dtStart As Date, dtEnd As Date) As Integer
    Dim dblDuration As Double
    Dim dtThis As Date 'used in loop
    If DateValue(dtStart) = DateValue(dtEnd) Then
        If Weekday(dtStart) Mod 6 <> 1 Then
            dblDuration = dtEnd - dtStart
        End If
     Else
        If Weekday(dtStart) Mod 6 <> 1 Then
            dblDuration = 1 - TimeValue(dtStart)
        End If
        For dtThis = dtStart + 1 To dtEnd - 1
            If Weekday(dtThis) Mod 6 <> 1 Then
                dblDuration = dblDuration + 1
            End If
        Next
        If Weekday(dtEnd) Mod 6 <> 1 Then
            dblDuration = dblDuration + TimeValue(dtEnd)
        End If
    End If
    WeekdayHours = dblDuration * 24
End Function

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Noah114]

Any way to exclude 12:00am to 7:00 am in a module?

 

[dhookom]

Sure, you would need to change this line
[tt][blue] dblDuration = dblDuration + 1[/blue][/tt]
to
[tt][blue] dblDuration = dblDuration + (17/24)[/blue][/tt]
Then make corrections for the first day and last day adds.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Noah114]

i did this, but it didn't work

Public Function WeekdayHours(dtStart As Date, dtEnd As Date) As Integer
Dim dblDuration As Double
Dim dtThis As Date 'used in loop
If DateValue(dtStart) = DateValue(dtEnd) Then
If Weekday(dtStart) Mod 6 <> 1 Then
dblDuration = dtEnd - dtStart
End If
Else
If Weekday(dtStart) Mod 6 <> 1 Then
dblDuration = 1 - TimeValue(dtStart)
End If
For dtThis = dtStart + 1 To dtEnd - 1
If Weekday(dtThis) Mod 6 <> 1 Then
dblDuration = dblDuration + (16 / 24)
End If
Next
If Weekday(dtEnd) Mod 6 <> 1 Then
dblDuration = dblDuration + TimeValue(dtEnd)
End If
End If
WeekdayHours = dblDuration * 24
End Function

 

[dhookom]

Why didn't it work? If you state that something doesn't work, you owe us a reason or result or something. What was wrong with your results? Did you make corrections for the first and last days?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

Furthermore, are you sure that excluding 12:00am to 7:00 am leaves 16 hours per working day ?

 

[Noah114]

Hi dhookom,

I apologize for the vague response on why it did not work, I ask my supervisor for the call center work hours which we are closed 11pm to 8am, which would be 8 hours which is why i inputed (16/24). After changing the "+1" to 16/24 I reran a practive run on one rep. I got the following results;

10/2/2006 1:37:13 PM 10/3/2006 3:43:15 PM 26.00

Could you assist me on what you mean by first and last days, I am a bit confused. Thank you sincerely for all of your help.

 

[dhookom]

What answer would you expect? If the answer is 18.1 then try this code

Public Function WeekdayHours(dtStart As Date, dtEnd As Date, _
    dtDayStart As Date, dtDayEnd As Date) As Double
    'dtStart is the starting date with time
    'dtEnd is the ending date with time
    'dtDayStart is the time work starts
    'dtDayEnd is the time work ends
    Dim dblDuration As Double
    Dim dtDayDuration As Date
    Dim dtTimeStart As Date
    Dim dtTimeEnd As Date
    dtTimeStart = TimeValue(dtStart)
    dtTimeEnd = TimeValue(dtEnd)
    If dtTimeStart < dtDayStart Then
        dtTimeStart = dtDayStart
    End If
    If dtTimeEnd > dtDayEnd Then
        dtTimeEnd = dtDayEnd
    End If
    Dim dtThis As Date 'used in loop
    'how long is a single day of work
    dtDayDuration = dtDayEnd - dtDayStart
    If DateValue(dtStart) = DateValue(dtEnd) Then
        If Weekday(dtStart) Mod 6 <> 1 Then
            dblDuration = dtTimeEnd - dtTimeStart
        End If
     Else
        If Weekday(dtStart) Mod 6 <> 1 Then
            dblDuration = 1 - TimeValue(dtStart)
        End If
        For dtThis = dtStart + 1 To dtEnd - 1
            If Weekday(dtThis) Mod 6 <> 1 Then
                dblDuration = dblDuration + dtDayDuration
            End If
        Next
        If Weekday(dtEnd) Mod 6 <> 1 Then
            dblDuration = dblDuration + dtTimeEnd - dtDayStart
        End If
    End If
    WeekdayHours = dblDuration * 24
End Function

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]