Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

MichaelRed's FAQ181-261 1

Status
Not open for further replies.
InfoNow

InfoNow

IS-IT--Management
Apr 20, 2001
106
US
Please help...trying to count number workdays between to dates. Using FAQ181-261, I was able to accomplished it, but now, I would like accuracy down to the number of hours. FAQ181-261 seems to round up/down a whole day. Is it possibe to calculate down to hours if the date fields have date and time in it( 4/29/2004 11:17:59 AM)? Of course, the no weekends/holidays rule still applies.

TIA
 
Sort by date Sort by votes
Could you provide some sample records and the expected results of your calculation. What you are asking makes no sense out of the context of your data.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.
 
Upvote 0 Downvote
TotalHours = [EndDateTime] - [StartDateTime]
for Example: TotalHours = [4/29/2004 11:17:59 AM] - [4/26/2004 03:17:59 AM]. Should weekends/holidays falls between the StartDateTime and EndDateTime, it would not count towards the TotalHours.

I hope that make sense...

Thanks
 
Upvote 0 Downvote
Hi InfoNow,

Just had a quick look at the FAQ and yes, it can be done, but it needs a slight amendment first.

The problem is that the counting of days is based partially on rounding of the Date/Time variables passed to it. If the variables contain times you may get funny results, for example:

28 Apr 2004 12:01pm to 29 Apr 2004 11:59am returns 1 day
28 Apr 2004 11:59am to 29 Apr 2004 11:59am returns 2 days
28 Apr 2004 11:59am to 29 Apr 2004 12:01pm returns 3 days

Apart from the inconsistent results, the way the code works means that there is not necessarily a check on whether the last (partial) day in the date range is a business day or not and, therefore, an adjustment for hours can not necessarily be made for that day. To remove this inconsistency, the Clng Functions in the loop control need changing to DateValue Functions so, firstly, change ..

Code: 
[purple]    For Idx = CLng(StartDate) To CLng(EndDate)[/purple]
.. to ..

Code: 
[blue]    For Idx = DateValue(StartDate) To DateValue(EndDate)[/blue]

Now, assuming you simply want a total count of hours (24 per work day, and not any kind of business-only hours), you can make the appropriate adjustment for hours at the beginning and end of the date range by adding a couple of extra checks; change ..

Code: 
[purple]    If (rstHolidays.NoMatch) Then
        NumDays = NumDays + 1
     Else
        [green]'Do Nothing, it is NOT a Workday[/green]
    End If
[/purple]
.. to ..

Code: 
[blue]    If (rstHolidays.NoMatch) Then
[red]        If DateValue(MyDate) = DateValue(StartDate) Then
            HoursAdjust = (DateValue(StartDate) - CDbl(StartDate)) * 24
        End If
        If DateValue(MyDate) = DateValue(EndDate) Then
            HoursAdjust = HoursAdjust + (DateValue(EndDate) - CDbl(EndDate)) * 24
        End If[/red]
        NumDays = NumDays + 1
     Else
        [green]'Do Nothing, it is NOT a Workday[/green]
    End If[/blue]

.. and then change the returned value to hours instead of days, and including the adjustment. So, rename the function by changing ..

Code: 
[purple]Public Function DeltaDays(StartDate As Date, EndDate As Date) As Integer[/purple]
.. to ..

Code: 
[blue]Public Function Delta[red]Hours[/red](StartDate As Date, EndDate As Date) As Integer[/blue]

.. and, finally, change ..

Code: 
[purple]    DeltaDays = NumDays[/purple]
.. to ..

Code: 
[blue]    Delta[red]Hours[/red] = CLng((NumDays) * 24 + HoursAdjust)[/blue]

I haven't tested this thoroughly, so please make sure it's correct before relying on it.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
 
Upvote 0 Downvote
Tony,

Thank you so much for your time...I will give this a try as soon as I get a chance today. Will report back.

Thanks again
 
Upvote 0 Downvote
Hi Tony,
It is giving me the wrong number of hours still. This is what's in my query...
NumHours: DeltaHours([RequestDATETIME],[CloseDATETIME])

Example1:
RequestDATETIME = 4/27/2004 10:26:17 AM
CloseDATETIME = 4/29/2004 10:26:59 AM
Returns: 68 hours instead of 48 hours.

Example2:
RequestDATETIME =4/27/2004 10:26:17 AM
CloseDATETIME = 4/30/2004 4:35:00 PM
Returns: 86 hours instead of 78 hours.

using DATEDIFF function the hours are correctly returns, but it will not work for me due to the weekends and holidays factor.
 
Upvote 0 Downvote
I would write code that would first calculate the total hours and then loop through the days to remove 24 hours for each holiday or weekend date. The only issue might be a start or ending date on a non-work date.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.
 
Upvote 0 Downvote
Hi InfoNow,

Sorry. Instead of the line ..

[purple]
Code: 
HoursAdjust = HoursAdjust + (DateValue(EndDate) - CDbl(EndDate)) * 24
[/purple]
.. try ..

Code: 
[blue]HoursAdjust = HoursAdjust + (CDbl(EndDate) - DateValue(EndDate) - 1) * 24[/blue]

I did say I hadn't tested it properly!


Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
 
Upvote 0 Downvote
Hi InfoNow,

Just after I posted I realised I'm making this more complex than I need.

Code: 
[blue]If (rstHolidays.NoMatch) Then
    
    If DateValue(MyDate) = DateValue(StartDate) Then
        HoursAdjust = [red]TimeValue(StartDate)[/red] * 24
    End If
    If DateValue(MyDate) = DateValue(EndDate) Then
        HoursAdjust = HoursAdjust + [red](1 - TimeValue(EndDate))[/red] * 24
    End If
    
    NumDays = NumDays + 1
 Else
[green]    'Do Nothing, it is NOT a Workday[/green]
End If
:
:
DeltaDays = CLng((NumDays) * 24 [highlight]-[/highlight] HoursAdjust)[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
 
Upvote 0 Downvote
Thank you so much Tony...It works beautifully!!
 
Upvote 0 Downvote
just curiosity, but it seems a bit coerced. what 'real world' situation does this represetnt? generally speaking, my experience has either not cared about the weekends / holidays or the requests was for an accurate calculation of working hours. the juxtaposition of including the full twenty-four hours for most days, but only the actual hours for the first and last day but then excluding the weekend & holiday dates is, at least, interesting


?????????????????????????????????





MichaelRed
mlred@verizon.net

 
Upvote 0 Downvote
Michael,
Love your FAQ!....Used it many times...Thanks.

We have 3 shifts that covers all 24 hours, Mondays thru Fridays. So, this is why I have decided to do it this way. If you can suggest a better resolution, I would appreciated it very much.

TIA
 
Upvote 0 Downvote
I haven't looked closely at TonyJollans soloution, but his work is good and you hqave already noted that it works for you.

I really was just curious about the situation. I've never been anywhere that ran three shifts, but closed for weekends and holidays. Closest to that for me was 'reduced' production requirements on holidays, where shift supervisors had hte option to reduce staff. Otherwise it has been much more comnventional, regular 5 or 6 woek days and the standard (9 or so) holidays.





MichaelRed
mlred@verizon.net

 
Upvote 0 Downvote
This code isn't working for me. It still computes the difference between 6/2/04 and 6/7/04 as 5 days. It should really be 3 at most. Any ideas where I might be going wrong?

Public Function DeltaDays(StartDate As Date, EndDate As Date) As Integer

'Get the number of workdays between given dates excluding weekends and holidays

Dim dbs As dao.Database
Dim rstHolidays As dao.Recordset

Dim Idx As Long
Dim MyDate 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)

MyDate = Format(StartDate, "Short Date")

For Idx = CLng(StartDate) To CLng(EndDate)
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 = "[HoliDate] = " & NumSgn & Format$(MyDate, "yyyy-mm-dd") & 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("d", 1, MyDate)

Next Idx

DeltaDays = NumDays


End Function
 
Upvote 0 Downvote
I would modify it slightly to:

Code: 
Public Function DeltaDays(StartDate As Date, EndDate As Date) As Integer

'Get the number of workdays between given dates excluding weekends and holidays

Dim dbs As dao.Database
Dim rstHolidays As dao.Recordset

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

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

NumSgn = Chr(35)
NumDays = 0

MyDate = Format(StartDate, "Short Date")

For Idx = CLng(StartDate) To CLng(EndDate)
  strCriteria = "[HoliDate] = " & NumSgn & Format$(MyDate, "yyyy-mm-dd") & NumSgn
  rstHolidays.FindFirst strCriteria
  If (rstHolidays.NoMatch) Then
    If (Weekday(MyDate) <> 1) OR (Weekday(MyDate) <> 7) then
      NumDays = NumDays + 1
    End If
  End If
  MyDate = DateAdd("d", 1, MyDate)
Next Idx

DeltaDays = NumDays

    
End Function

Leslie
 
Upvote 0 Downvote
The code works for me and returns 4 days. I would re-write the code to cut out all the shifting between data types. You can also simplify the Select Case.
Code: 
Public Function DeltaDays(StartDate As Date, EndDate As Date) As Integer

'Get the number of workdays between given dates excluding weekends and holidays

    Dim dbs As DAO.Database
    Dim rstHolidays As DAO.Recordset
    
    Dim MyDate 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)
    
    MyDate = Format(StartDate, "Short Date")
    
    For MyDate = StartDate To EndDate
        Select Case (Weekday(MyDate))
        Case 1, 7 ' Sunday or Saturday
        'Do Nothing, it is NOT a workday
        
        Case Else 'Normal Workday
            strCriteria = "[HoliDate] = " & NumSgn & Format$(MyDate, "yyyy-mm-dd") & NumSgn
            rstHolidays.FindFirst strCriteria
            If (rstHolidays.NoMatch) Then
                NumDays = NumDays + 1
            Else
                'Do Nothing, it is NOT a workday
            End If
        End Select
    Next
    'close the objects and set to nothing
    rstHolidays.Close
    Set rstHolidays = Nothing
    Set dbs = Nothing
    DeltaDays = NumDays
End Function

Duane
MS Access MVP
Find out how to get great answers faq219-2884.
 
Upvote 0 Downvote
Thanks Duane. It seems to be working fine now. The only thing I notice is that it seems to be adding on an extra day. For instance, deltadays between 6/2/4 and 6/9/4 comes out to be 6 with the code above which is fine if you are wanting to count straight days. But I believe 5 would make more sense if you are actually looking to count the time elapsing between the two. I believe the hour rounding is the culprit. If someone is assigned something on 6/2/4 (usually midday), then by 6/9/4 (this check would most likely occur at midday) they've been assigned it for 5 days.

I modified the code above slightly by just subtracting a 1 from the NumDays at the end.

...
DeltaDays = NumDays - 1
End Function

Would that modification have any ramifications down the road that I'm not considering?

Thanks for everyones help!
 
Upvote 0 Downvote
It will have some ramifications but as long as you are aware of the business logic, you should be ok. When ever I create functions like this, I do lots of testing to confirm the logic and code are correct.

I think the logic of the original function is similar to the SQL "Between x And y" where the values x and y are both included in the returned data set.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.
 
Upvote 0 Downvote
Additional question: I have implemented the above module into my database and it works wonderfully. However, now they want me to figure out the date that will be 60 working days from a known date. Any ideas on how I can set this up using the above or do I need to create a new module for this function? Any advice would be greatly appreciated. Thanks
 
Upvote 0 Downvote
There is another faq re this. see faq705-3213





MichaelRed
mlred@verizon.net

 
Upvote 0 Downvote
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top