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