?
Solved

Calculate timelapse without weekend hours

Posted on 2003-03-13
5
Medium Priority
?
300 Views
Last Modified: 2010-05-01
hello all!

For a current assignment I need to calculate the amount of hours between two points in time (not too difficult), but keeping in mind that

- these two points can lie as much as a couple of months apart
- the hours between saturdays 06:00 and mondays 06:00 do not count
- these "weekend begin and end times" could be changed
- this is a VBA (Access) environment

I rated this question 100 points: should not be too difficult (eventhough I have no idea), but is rather urgent...

Any tips would be welcomed!

Ozzy
0
Comment
Question by:Ozzymandias
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 

Expert Comment

by:SlingBlade
ID: 8130145
So basically a payroll calculation...

Do you still need the answer to this or have you missed the deadline?

*
0
 

Expert Comment

by:R2DTOEA
ID: 8130541
Dim lHours as long

dteFutureDate = whatever value
dteCurDay = dteFirstDate

Do until datediff("d", dteCurDay, dteFutureDate) = 0
    if weekday(dtecurday) <> vbSaturday and weekday(dtecurday) <> vbSunday then
         'replace 12 with the number of hours you need
         'for the day if you are counting all 24, except
         'for the few hours before 6:00am Monday then
         'include the following commented out if statement
         'if weekday(dtecurday) <> vbmonday then
              lHours = lhours + 24
         'else
         '    lhours = lhours + 18
         'end if
    end if
    dtecurday = dateadd("d", 1, dtecurday)
    doevents
loop
0
 

Expert Comment

by:R2DTOEA
ID: 8130589
The above code will count everything except for the weekends.  Taking out the comments around the commented if statement will also not count the four hours before 6:00am.  

If you need help altering it to what you are asking about, just let me know, but the above should give you enough info to get there on your own.
0
 

Accepted Solution

by:
R2DTOEA earned 300 total points
ID: 8130598
Dim lHours as long

dteFutureDate = whatever value
dteCurDay = dteFirstDate

Do until datediff("d", dteCurDay, dteFutureDate) = 0
    if weekday(dtecurday) <> vbSaturday and weekday(dtecurday) <> vbSunday then
         'replace 12 with the number of hours you need
         'for the day if you are counting all 24, except
         'for the few hours before 6:00am Monday then
         'include the following commented out if statement
         'if weekday(dtecurday) <> vbmonday then
              lHours = lhours + 24
         'else
         '    lhours = lhours + 18
         'end if
    end if
    dtecurday = dateadd("d", 1, dtecurday)
    doevents
loop
0
 

Author Comment

by:Ozzymandias
ID: 8135325
R2DTOEA,
thanx for the quick response. In the mean time I have been able to solve the problem. Your answer did point me somewhat in the right direction, but the thing is that the begin and end time of a work week are retreived from a GlobalOptions table, in the format "Monday 06:00" to "Saturday 06:00".
So I had some trouble calculating the number of hours between these two points. Doing the math in your head for this is easy, but in Access... Here's how I did it:


Public Function DetermineWeekendHours(strWeekBegin As String, strWeekEnd As String) As Double

Dim dayBegin, hrsBegin As String    'Split up strWeekBegin in days and hrs
Dim dayEnd, hrsEnd As String      'Split up van strWeekEnd in days and hrs
Dim datNxtWeekBegin As Date         'Date next "begin workweek"
Dim datNxtWeekEnd As Date          'Date next "end workweek"
Dim i As Integer

dayBegin = Left(strWeekBegin, InStr(strWeekBegin, " ") - 1)
hrsBegin = Mid(strWeekBegin, InStr(strWeekBegin, " ") + 1)

dayEnd = Left(strWeekEnd, InStr(strWeekEnd, " ") - 1)
hrsEnd = Mid(strWeekEnd, InStr(strWeekEnd, " ") + 1)

For i = 1 To 7 Step 1
    If WeekdayName(Weekday(DateAdd("d", i, Now()), vbMonday), False, vbMonday) = dayEnd Then
        datNxtWeekEnd = Format(DateAdd("d", i, Now()), "medium date")
        Exit For
    End If
Next i
For i = 1 To 7 Step 1
    If WeekdayName(Weekday(DateAdd("d", i, datNxtWeekEnd), vbMonday), False, vbMonday) = dayBegin Then
        datNxtWeekBegin = Format(DateAdd("d", i, datNxtWeekEnd), "medium date")
        Exit For
    End If
Next i

DetermineWeekendHours = (DateDiff("d", datNxtWeekEnd, datNxtWeekBegin, vbMonday) * 24) + _
                 (DateDiff("n", CDate(hrsEnd), CDate(hrsBegin)) / 60)

End Function

The rest was rather simple : determine the total number of hours between the two points in time, calculate the number of weekends between the two, and substract the number of WeekendHours from the total.

So, thanx again for your tips!

Ozzy
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question