Calculate time worked

[frenziedfemale]

Hi all,

I have a form that we enter the Received Date and time in one text box and the finalised date and time in the next text box. I need to have a text box that calculates the elapsed time in hours and minutes, excluding weekends and hours not included in a work day (i.e. we work a 7.6 hour workday between 8.30am and 5.00pm).

I used the Working Days Module and set up a holiday table for public holidays here. It works but calculates any part of a day as 1 day. I need hours and minutes as we are trying to use the data to create our Service Level Agreements etc.

Any help would be greatly appreciated. I am very much a novice and have been basically copy and pasting codes to get where I need to be.

Thanks in advance,

Kim

[HansV]

Welcome to Eileen's Lounge!

8:30 AM to 5:00 PM is 8.5 hours; you mention that a workday is 7.6 hours, so I assume that the 0.9 hours difference is for breaks. Do you have fixed times for breaks?

[HansV]

Another question: will the received time and finalised time always be on Mon-Fri between 8:30 AM and 5:00 PM?

[frenziedfemale]

Hi Hans,

Thanks for the welcome :)

Break times are generally at 10.30am and 1pm. The 1pm break is 30 mins and the 10.30 break could be set for the remainder of the .9 hours.

The received time can be anytime but the finalised time is always between Mon-Fri between 8:30 AM and 5:00 PM.

Thanks,
Kim

[HansV]

Try this version. It assumes you have a table tblHolidays with a field HolidayDate.

Please test carefully!

Code: Select all

Function WorkingHours(StartDate As Date, EndDate As Date) As Double
    '....................................................................
    ' Original Name: WorkingDays2
    ' Author: Arvin Meyer
    ' Modified by Eileen's Lounge to take times into account
    '....................................................................
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim d As Date
    Dim sd As Date
    Dim st As Date
    Dim ed As Date
    Dim et As Date
    Dim n As Long
    Dim f As Boolean
    Dim t As Double

    On Error GoTo ErrHandler
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("tblHolidays", dbOpenSnapshot)
    sd = Int(StartDate)
    st = StartDate - sd
    If st < #8:30:00 AM# Then
        st = #8:30:00 AM#
    ElseIf st > #5:00:00 PM# Then
        sd = sd + 1
        st = #8:30:00 AM#
    End If
    ed = Int(EndDate)
    et = EndDate - ed
    d = sd
    f = True
    Do While d < ed
        If Weekday(d, vbMonday) <= 5 Then
            rst.FindFirst ("HolidayDate = #" & Format(d, "mm/dd/yyyy") & "#")
            If rst.NoMatch Then
                n = n + 1
                f = False
            ElseIf f Then
                st = #8:30:00 AM#
            End If
        ElseIf f Then
            st = #8:30:00 AM#
        End If
        d = d + 1
    Loop
    If et > st Then
        t = 24 * (et - st) - 0.4 * (st <= #10:30:00 AM#) * (et >= #10:54:00 AM#) - 0.5 * (st <= #1:00:00 PM#) * (et > #1:30:00 PM#)
    ElseIf et < st Then
        t = 24 * (et - st) + 0.4 * (st <= #10:30:00 AM#) * (et >= #10:54:00 AM#) + 0.5 * (st <= #1:00:00 PM#) * (et > #1:30:00 PM#)
    End If
    WorkingHours = 7.6 * n + t

ExitHandler:
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    Exit Function

ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
End Function

The function returns time as decimal hours, e.g. 6:30 is returned as 6.5

[frenziedfemale]

Hi Hans,

Thank you. That is heading towards what I was hoping to achieve. Sorry to be painful, but, could you tell me which part of the code do i need to remove for the break times, we want to record the whole 8.5 hours between 8.30am and 5.00pm without breaks. And is it possible to have the result in minutes instead of decimal hours?

I despair of ever understanding all of the bits and pieces that Access has.

Thanks in advance,

Kim

[HansV]

The function becomes slightly simpler:

Code: Select all

Function WorkingMinutes(StartDate As Date, EndDate As Date) As Long
    '....................................................................
    ' Original Name: WorkingDays2
    ' Author: Arvin Meyer
    ' Modified by Eileen's Lounge to take times into account
    '....................................................................
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim d As Date
    Dim sd As Date
    Dim st As Date
    Dim ed As Date
    Dim et As Date
    Dim n As Long
    Dim f As Boolean
    Dim t As Double

    On Error GoTo ErrHandler
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("tblHolidays", dbOpenSnapshot)
    sd = Int(StartDate)
    st = StartDate - sd
    If st < #8:30:00 AM# Then
        st = #8:30:00 AM#
    ElseIf st > #5:00:00 PM# Then
        sd = sd + 1
        st = #8:30:00 AM#
    End If
    ed = Int(EndDate)
    et = EndDate - ed
    d = sd
    f = True
    Do While d < ed
        If Weekday(d, vbMonday) <= 5 Then
            rst.FindFirst ("HolidayDate = #" & Format(d, "mm/dd/yyyy") & "#")
            If rst.NoMatch Then
                n = n + 1
                f = False
            ElseIf f Then
                st = #8:30:00 AM#
            End If
        ElseIf f Then
            st = #8:30:00 AM#
        End If
        d = d + 1
    Loop
    t = 1440 * (et - st)
    WorkingMinutes  = 510 * n + t

ExitHandler:
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    Exit Function

ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
End Function

[Wendell]

Hi Hans,
...
I despair of ever understanding all of the bits and pieces that Access has.

Thanks in advance,

Kim

No worries there - I've worked extensively with Access from 1994 to 2015 and I am regularly surprised by something I didn't know about Access - and I suspect Hans would say something similar. The trick is to find out what you need to know to solve the problem at hand.

[HansV]

I fully agree, Wendell!

[frenziedfemale]

Thanks for the reassurance, Wendell. I only learnt access in October 2016 so I feel like i'm treading water all the time. Glad to know it is so full of surprises.

Hans, thank you so much for your patience. I really appreciate your help.

Many, many thanks,
Kim

[frenziedfemale]

Hans,

I can not tell you how much your help has made my life easier. The code works perfectly (of course!). You are amazing!

Thanks again,
Kim

[HansV]

Glad to hear that. Thanks for the feedback!

[frenziedfemale]

Hi Hans,

Sorry to bother you again

I am now at the stage of writing a query to use to create a report (I hope this is the right process!). Is it possible to use the Working Minutes function in a field in the query to have the query do the same calculation as it does on the form? I have tried to add it but get an error that is: 'The expression contains an ambiguous name. Verify that each name in the expression refers to a unique object'

I dont want to have to keep extracting all the info to an excel spreadsheet and manipulating the data to get the information i need. I have attached the spreadsheet which is my ultimate goal for my report. I would attach my dB but even after zipping it is too big, apparently.

Thanks,
Kim

[HansV]

The error message indicates that you may have created two functions with the same name, or perhaps given a code module the same name as the function. Please check that carefully.

If you'd still like to make the database available, you can upload it to a cloud service such as DropBox, Google Drive or OneDrive, share it and post a link to the uploaded and shared file in a reply.

[frenziedfemale]

Thanks, Hans! That was my problem (well one of many!)

I have attached a word document with a query I am trying to write so that I can then use two queries, one for the main form and one for the subform, to write a report. Could you possibly tell me how to write the expression that I have outlined?

Thanks again,

Kim

[HansV]

You could create a new query based on the one you have.
Add QueryID and Expr1 to the query grid (I would provide a more meaningful name instead of Expr1).
Click to highlight the Totals button in the Show/Hide group of the Design tab of the ribbon.
This will add a Total: row to the query design grid.
Leave the Total option for QueryID set to Group By, and set that for Expr1 to Sum.

You mentioned higher up in this thread that "the finalised time is always between Mon-Fri between 8:30 AM and 5:00 PM", so I didn't take the situation that both LoginTime and LogoutTime are outside normal hours into account.

[frenziedfemale]

Hi Hans,

I have created the new query as you have suggested.

When I add the Total Row and set the Expression to Sum I get the mismatch of criteria error.

I have tried changing the fields etc to no avail. Here is the link to the database, if you have time could you point me in the right direction....again :(

https://1drv.ms/f/s!Am1IPP3VwfrXmBBZMJmqdu5Y5CRh

Thanks,

Kim

[HansV]

Your table tblTimeLog contains records for which either LoginTime or LogOutTime is blank. The WorkingMinutes function cannot calculate working minutes for those records, so it returns #Error. To get around this, add criteria to both LoginTime and LogOutTime in qryTimeLog:

Is Not Null

This excludes all records for which the working minutes cannot be calculated. You can then change qryLoggedMinutesbyQueryNumber to a Totals query.

PS You have created the queries in the backend - I would move them to the frontend.

[frenziedfemale]

Hi Hans,

Thank you, thank you, thank you!

I knew it had to be something simple! It was similar to not seeing the forest for the trees!

I appreciate your help so much and would not have learnt as much or made it this far without you.

Thank you again,

Kim

[HansV]

You're welcome! Glad to have been able to help.