Calendar Work

[Remington]

Here is the code I have so far.

Private Sub Command17_Click()
    Dim intHours As Double
    Dim intTrial As Double
 
    Me.DaysGone = DateDiff("d", [LeaveDate], [ReturnDate])
 
    intHours = DaysGone * 8
 
    HoursGone = intHours
End Sub

This is a Calendar Plugin that I have set up to find the difference between the two dates. The Idea is to select the first date as the day an employee left work, and have the second date as the date the employee returns to work. I am trying to figure out how to get the right amount of days, with the weekends being factored out. At the moment, if you leave on a monday and return on a monday, you are gone for 7 days. I need this to only be 5 days.

Here is a line of code that someone previously suggested:

'MoveWD moves datThis on by the intInc weekdays.
Public Function MoveWD(datThis As Date, intInc As Integer) As Date
    MoveWD = datThis
    For intInc = intInc To Sgn(intInc) Step -Sgn(intInc)
        MoveWD = MoveWD + Sgn(intInc)
        Do While (Weekday(MoveWD) Mod 7) < 2
            MoveWD = MoveWD + Sgn(intInc)
        Loop
    Next intInc
End Function

This code looks like it could be useful, but my knowledge of how to implement it into my program is very limited. Perhaps there is an easier way to acomplish this? Could I use some simple math to take away 2 days from every 7 that are found with my DateDiff?

Any help is most appreciated.

Thank you

[MMcCarthy]

Try this ...

Private Sub Command17_Click()
    Dim intHours As Double
    Dim tmpDate As Date
Dim tmpDays As Integer
 
   tmpDate = Me!LeaveDate
   Do until tmpDate = Me!ReturnDate
      If Weekday(tmpDate) NOT IN (6, 7) Then
         tmpDays = tmpDays + 1
      End If
      tmpDate = tmpDate + 1
   Loop
 
   Me.DaysGone = tmpDays
 
   intHours = DaysGone * 8
 
   HoursGone = intHours
 
End Sub

This function actually moves forward or backwards through weekdays. I don't think it's what you're looking for here.

'MoveWD moves datThis on by the intInc weekdays.
Public Function MoveWD(datThis As Date, intInc As Integer) As Date
    MoveWD = datThis
    For intInc = intInc To Sgn(intInc) Step -Sgn(intInc)
        MoveWD = MoveWD + Sgn(intInc)
        Do While (Weekday(MoveWD) Mod 7) < 2
            MoveWD = MoveWD + Sgn(intInc)
        Loop
    Next intInc
End Function

[Remington]

Thanks so much for the help. I think this should work, but I am getting an error.

Do Until tmpDate = Me!ReturnDate
      If Weekday(tmpDate) NOT IN (6, 7) Then
         tmpDays = tmpDays + 1

The "If Weekday" line is showing up in red, and I get a syntax error. Is this because Weekday is not defined? Should "weekday" be the Box that my calendar date is put into?

[MMcCarthy]

Thanks so much for the help. I think this should work, but I am getting an error.

Do Until tmpDate = Me!ReturnDate
      If Weekday(tmpDate) NOT IN (7, 1) Then
         tmpDays = tmpDays + 1

The "If Weekday" line is showing up in red, and I get a syntax error. Is this because Weekday is not defined? Should "weekday" be the Box that my calendar date is put into?

Weekday() is an Access function.

Try this ... (By the way I got the 6 and 7 wrong it should have been 1 and 7)

If Weekday(tmpDate) <> 1 And  Weekday(tmpDate) <> 7) Then
      tmpDays = tmpDays + 1
End If

Mary

[Remington]

Woo Hoo! Much Praise to Mary. I finaly got it to work. I just had to remove the ) from the 7). Just a few touch ups and I think this small database will be ready to leave my hands.

Thanks Again.

Todd

[MMcCarthy]

Woo Hoo! Much Praise to Mary. I finaly got it to work. I just had to remove the ) from the 7). Just a few touch ups and I think this small database will be ready to leave my hands.

Thanks Again.

Todd

You're welcome Todd. Sorry about the closing bracket. Copying and pasting again :)

Mary