Code for Business Hours -Help Please

[arnelgp]

i hope this one will get it right:

Public Function ExtractionHours(d1 As Date, d2 As Date) As Double
    Const timeStart As String = " 8 am"
    Const timeEnd As String = " 5 pm"
    Const hr1 As Double = 4.16666666715173E-02
    Const Min1 As Double = 6.94444444444442E-04

    Dim dDate As Date
    Dim timeInMin As Double
    If d1 > d2 Then
        dDate = d1
        d1 = d2
        d2 = dDate
    End If
    
    Select Case (True)
        Case DateValue(d1) = DateValue(d2)
                
            If InStr(1, "Saturday, Sunday", Format(d1, "dddd")) = 0 Then
                d1 = maxAmong(d1, CDate(DateValue(d1) & timeStart))
                dDate = minAmong(d2, CDate(DateValue(d2) & timeEnd))
                timeInMin = CDec(dDate) - CDec(d1)
            
            End If
            
        Case DateDiff("d", CDate(DateValue(d1)), CDate(DateValue(d2))) = 1
        
            
            If InStr(1, "Saturday, Sunday", Format(d1, "dddd")) = 0 Then
                d1 = maxAmong(d1, CDate(DateValue(d1) & timeStart))
                timeInMin = timeInMin + CDec(CDate(DateValue(d1) & timeEnd)) - CDec(d1)
            End If
            If InStr(1, "Saturday, Sunday", Format(d2, "dddd")) = 0 Then
                d2 = minAmong(d2, CDate(DateValue(d2) & timeEnd))
                    timeInMin = timeInMin + CDec(d2) - CDec(CDate(DateValue(d2) & timeStart))
            End If
            
        Case Else
        
            If InStr(1, "Saturday, Sunday", Format(d1, "dddd")) = 0 Then
                d1 = maxAmong(d1, CDate(DateValue(d1) & timeStart))
                timeInMin = timeInMin + CDec(CDate(DateValue(d1) & timeEnd)) - CDec(d1)
            End If
            If InStr(1, "Saturday, Sunday", Format(d2, "dddd")) = 0 Then
                d2 = minAmong(d2, CDate(DateValue(d2) & timeEnd))
                    timeInMin = timeInMin + CDec(d2) - CDec(CDate(DateValue(d2) & timeStart))
            End If
            For dDate = CDate(DateValue(d1)) + 1 To CDate(DateValue(d2)) - 1
                If InStr(1, "Saturday, Sunday", Format(dDate, "dddd")) = 0 Then
                    timeInMin = timeInMin + (9# * hr1)
                End If
            Next
            
    End Select
        
    If timeInMin > 0 Then
        ExtractionHours = Round(timeInMin / 60 / Min1, 2)
    End If
            
End Function

 

[Lilly420]

Thank you again for all your help...so with this new code, I now get the 13 hours and all the others look correct too, except the first one, see below...and it was correct with the other code, I got 9 hours...now it is 10.

? ExtractionHours (#8/1/2019 8AM#, #8/2/2019 9AM#) 10 (8 hours day 1 and 1 hour day2?)
? ExtractionHours (#8/1/2019 9AM#, #8/2/2019 1PM#) 13

Our day depends on when you come in anywhere from 7am to 4pm or 8am to 5pm...I just thought if we did away with the breaks and someone entered there time in it would not include that hour break..forgive me if I am not understanding this correctly.

And, you are wonderful to help me. Thank you.

Lilly

 

[theDBguy]

Our day depends on when you come in anywhere from 7am to 4pm or 8am to 5pm...I just thought if we did away with the breaks and someone entered there time in it would not include that hour break..forgive me if I am not understanding this correctly.

And, you are wonderful to help me. Thank you.

Lilly

Hi Lilly. This is just for my own sanity. In earlier examples you gave, didn't you provide a start time of 9AM? If so, then you're saying it's possible for anyone to come in at any time during the day, correct? However, in quoted post above, I gather you're saying no one will come in earlier than 7AM and leave later than 5PM, right? If so, could you please define the total hours you expect to count for the following individuals:

start    end
7am      4pm (I'm guessing 8 hrs)
8am      5pm (also 8 hrs, right?)
9am      5pm
8am      4pm
9am      4pm
7am      1pm
12pm     4pm
12pm     5pm
7am      5pm (will this ever happen?)

 

[Lilly420]

Hi,

Some in my group come in at 7am and some at 8am and we are all paid for 8 hours, we are all salary so we don't punch in and out, most of us work 9 hours a day and some leave at 4 and some at 5...so is this what you are asking?

Lilly

 

[Lilly420]

The 9am is when we started the Monarch Extraction if that is what you are asking, not when we came in. We are trying to gauge the amount hours it takes to do an extraction for a customer.

 

[theDBguy]

Hi,

Some in my group come in at 7am and some at 8am and we are all paid for 8 hours, we are all salary so we don't punch in and out, most of us work 9 hours a day and some leave at 4 and some at 5...so is this what you are asking?

Lilly

Okay, put another way, since you're all salaried employee, is it safe to say everybody gets paid 8 hrs per day whether they work 8 hours or more or less? If so, then the total number of hours should be as simple as number of days worked/clocked in times 8. As for the monarch one, I would probably suggest to just calculate the raw hours between start and finish. Meaning, pretend the "break" is included in the work time. Just my thoughts...

 

[Mark_]

The 9am is when we started the Monarch Extraction if that is what you are asking, not when we came in. We are trying to gauge the amount hours it takes to do an extraction for a customer.

Several relevant questions;
1) Is any of the extraction automated so it can be done while a person is on a break?
2) Can more than one person be involved with one extraction?
3) Do you encounter instances where there is a break in processing that needs to be accounted for? i.e. extraction starts at 9am but you have something come up and have to stop at 11am, but restart at 3:30pm and finish at 11:30am the next day?

This is as important as your "Breaks" issue and may give a solution for how you wish to handle them.

 

[arnelgp]

ok, I think its complicated so I am moving away... for awhile.

emp comes anywhere from 7am-4pm or 8am-5pm.
extraction begins at 9am or any time thereafter.
so some of the sample time I encounter begins with 8am or 9am or 7am.
what are the base time for these, the 7am-4am group or the 8am-5pm or 9am?
some more extractions begin later, 2pm.
anyone can do the extraction, but to which base time to use, we don't know.

 

[Lilly420]

Hello,

I know, I don't mean to frustrate you and I so appreciate your help. Sorry.

We work from the earliest of 7am to the latest of 5pm.

We do extractions any time of the day. We get a request from the customer they need an extraction, so I would log in to the DB when I started which could be any time and when I finished it. If I start late like say 3:00pm and don't finish that day, I would enter the next day and the time that I completed the extract as my finish time...does this make sense? Most times, we finish extracts within 2 hours, and some even less time but there are a few that take longer...especially if we have to build new models for new customers. We are trying to keep track of the time we spend doing these extractions by customer and by the person doing the extract. I hope this helps.

Lilly

 

[Lilly420]

to answer Mark's question...we would only enter in the start date and time and then the finish date and time, we don't need to allow for breaks, if the person doing the extract needs to stop for another reason, they can keep track of that on their own...but in most cases, once we start the extract, we finish it as customers are waiting for them.

No extraction is automated, we use Monarch as our tool to do these extractions.

 

[theDBguy]

Hello,

I know, I don't mean to frustrate you and I so appreciate your help. Sorry.

We work from the earliest of 7am to the latest of 5pm.

We do extractions any time of the day. We get a request from the customer they need an extraction, so I would log in to the DB when I started which could be any time and when I finished it. If I start late like say 3:00pm and don't finish that day, I would enter the next day and the time that I completed the extract as my finish time...does this make sense? Most times, we finish extracts within 2 hours, and some even less time but there are a few that take longer...especially if we have to build new models for new customers. We are trying to keep track of the time we spend doing these extractions by customer and by the person doing the extract. I hope this helps.

Lilly

Hi Lilly. I decided to give it a try, so here's my attempt at a simple approach. I hope it gets you closer.

Public Function TotalWorkHours(StartTime As Date, EndTime As Date) As Double
'thedbguy@gmail.com
'8/2/2019

Const WorkStart As Date = #8:00:00 AM#
Const WorkEnd As Date = #5:00:00 PM#

Dim dblTime As Double 'counter
Dim dblHours As Double 'total
Dim dteStart As Date
Dim dteEnd As Date
Dim lngDays As Long

dteStart = DateValue(StartTime)
dteEnd = DateValue(EndTime)
lngDays = DateDiff("d", StartTime, EndTime)

'calculate first day hours
If StartTime >= dteStart + WorkStart Then
    If EndTime <= dteStart + WorkEnd Then
        dblTime = DateDiff("n", StartTime, EndTime)
    Else
        dblTime = DateDiff("n", StartTime, dteStart + WorkEnd)
        If dblTime > 8 * 60 Then dblTime = 8 * 60 'adjust to 8 hours work day
    End If
Else
    If CDbl(EndTime) <= CDbl(dteStart + WorkEnd) Then
        dblTime = DateDiff("n", dteStart + WorkStart, EndTime)
        If dblTime > 8 * 60 Then dblTime = 8 * 60 'adjust to 8 hours work day
    Else
        dblTime = DateDiff("n", dteStart + WorkStart, dteStart + WorkEnd)
        If dblTime > 8 * 60 Then dblTime = 8 * 60 'adjust to 8 hours work day
    End If
End If

'save calculation and reset counter
dblHours = dblTime
dblTime = 0

'calculate last day hours
If dteStart = dteEnd Then
    'already done above
Else
    If EndTime >= dteEnd + WorkStart Then
        If CDbl(EndTime) <= CDbl(dteEnd + WorkEnd) Then
            dblTime = DateDiff("n", dteEnd + WorkStart, EndTime)
            If dblTime > 8 * 60 Then dblTime = 8 * 60 'adjust to 8 hours work day
        Else
            dblTime = 8 * 60
        End If
    End If
End If

'add up the calculated hours and reset counter
dblHours = dblHours + dblTime
dblTime = 0

'calculate full days
If lngDays > 1 Then
    dblHours = dblHours + (lngDays - 1) * 8 * 60
End If

'return total hours
TotalWorkHours = dblHours / 60

End Function

 

[Lilly420]

Hello there,

Thank you for trying, it works if it is within the same day, but if they are different dates it does not...see below-1st example:

? TotalWorkHours (#8/1/2019 3pm#, #8/2/2019 10AM#) 2 -should be 4
? TotalWorkHours (#8/1/2019 3pm#, #8/1/2019 4pm#) 1

Thank you for trying to help. Appreciate it.

Lilly

 

[theDBguy]

Hello there,

Thank you for trying, it works if it is within the same day, but if they are different dates it does not...see below-1st example:

? TotalWorkHours (#8/1/2019 3pm#, #8/2/2019 10AM#) 2 -should be 4
? TotalWorkHours (#8/1/2019 3pm#, #8/1/2019 4pm#) 1

Thank you for trying to help. Appreciate it.

Lilly

Hi Lilly. Not sure why you get 2, I get 4 as shown below:

 

[Lilly420]

The code given by Arnelgp is working great just not on one of the dates...and not sure why that is, it's strange...

? ExtractionHours (#8/1/2019 8am#, #8/2/2019 9AM#) 10 should be 9

but yet this works...

? ExtractionHours (#8/1/2019 10am#, #8/2/2019 9AM#) 8

Thanks again, everyone .. appreciate you all.

Lilly

 

[Lilly420]

Oh my, I just don't know...I copied and pasted the code just as you have it and am only getting 2...I have no idea why that is.

Lilly

 

[theDBguy]

The code given by Arnelgp is working great just not on one of the dates...and not sure why that is, it's strange...

? ExtractionHours (#8/1/2019 8am#, #8/2/2019 9AM#) 10 should be 9

but yet this works...

? ExtractionHours (#8/1/2019 10am#, #8/2/2019 9AM#) 8

Thanks again, everyone .. appreciate you all.

Lilly

Hi Lilly. Did you see my post earlier? Here's what I get with the same dates you used above.

 

[Lilly420]

Hi, I did see your post, and I have no idea what is wrong...I copied and pasted your code into a new module and am not getting the correct hours for some of them like you are...I have no idea why...any ideas?

Lilly

 

[theDBguy]

Oh my, I just don't know...I copied and pasted the code just as you have it and am only getting 2...I have no idea why that is.

Lilly

Hi Lilly. Just for fun, download the attached and try some dates in the Immediate Window first to check if it works. Then, try importing the module into your db to see if the problem still exists.

 

[Lilly420]

Hi again, I will do this when I get home, I can't download anything on my company laptop for security purposes. I will do this and let you know what I find out. Again, appreciate all your help...

Lilly

 

[theDBguy]

Hi again, I will do this when I get home, I can't download anything on my company laptop for security purposes. I will do this and let you know what I find out. Again, appreciate all your help...

Lilly

Okay. Please let us know how it goes.