hi, everybody
Sir, I have some problem. Please help me to solve these problems
i have two fields namely 1) date of leave 2) date of joining
please tell me the difference between two date.

thanking you

DateDiff()

Do a search for DateDiff() in VBA Help. Basically the syntax is:

DateDiff("d", [LeaveDate], [JoinDate])

This will tell you the number of days between the two dates, for example. You can use this in a query by creating a new column and putting this on the Field line:

DaysOut:(DateDiff("d", [LeaveDate], [JoinDate]))

This adds a column to your query called "DaysOut" that returns the number of days (not workdays.) Weekdays only is:

DaysOut:(DateDiff("w", [LeaveDate], [JoinDate]))

Did that help?





mmcdonal

thank you very much mmcdonal.

Hi mmcDonal
Is there any way of reversing the procedure on dates .
Say you have the start date and the number of days and you need to know the date say in 60 days from the start date in workdays.Is this possible

Thanks


Brendan Bartley

There may be more elegant ways of doing this, but I am a brute force coder, so what I would do is start with the start date and check the day value. If it is a 1 or a 7, then do not increment the workday counter, but if it is a 2, 3, 4, 5, 6 increment the workday counter by 1, then add a day, and then loop through again. So:

dtStart = Date() 'takes today's date.
dtEnd = dtStart 'starts the end date counter
i = 0
Do Until i = 60 'trust me, it's 60, not 59
   iDay = DatePart("w", dtEnd) 'day of week
     If iDay <> 1 And iDay <> 7 Then 'not Sat or Sun
        i = i + 1
     End If
   dtEnd = DateAdd("d", 1, dtEnd) 'add a day
Loop
iDay = DatePart("w", dtEnd) 'check the last day
If iDay = 1 Then 'to make sure it is not a SAt or Sun
    dtEnd = DateAdd("d", 1, dtEnd) 'Sun
ElseIf iDay = 7 Then
    dtEnd = DateAdd("d", 2, dtEnd) 'Sat
End If

'Output:
dtStart 'your original start date
dtEnd 'end date 60 workdays from dtStart

HTH

mmcdonal

If you were feeding in a random number of workdays, then you would take the random number either from a text box on the form, or from an input box. So that would look like:

Dim iNumOfDays As Integer

iNumOfDays = Me.txtInputTextBox

'Then add to the code like this:

dtStart = Date() 'takes today's date.
dtEnd = dtStart 'starts the end date counter
i = 0
Do Until i = iNumOfDays
   iDay = DatePart("w", dtEnd) 'day of week
     If iDay <> 1 And iDay <> 7 Then 'not Sat or Sun
        i = i + 1
     End If
   dtEnd = DateAdd("d", 1, dtEnd) 'add a day
Loop
iDay = DatePart("w", dtEnd) 'check the last day
If iDay = 1 Then 'to make sure it is not a SAt or Sun
    dtEnd = DateAdd("d", 1, dtEnd) 'Sun
ElseIf iDay = 7 Then
    dtEnd = DateAdd("d", 2, dtEnd) 'Sat
End If

'Output:
dtStart 'your original start date
dtEnd 'end date 60 workdays from dtStart


mmcdonal

mmcdonal
The code is working fine. Is there any way of not counting say 25th December Christmas day to exclude it from working days.

Thanks


Brendan Bartley

Yes. In this part of the code:

     dtEnd = DateAdd("d", 1, dtEnd) 'add a day
Loop

Add this:

     dtEnd = DateAdd("d", 1, dtEnd) 'add a day
     sEnd = Left(dtEnd, 5)
       If sEnd = "12/25" Then
          dtEnd = DateAdd("d", 1, dtEnd) 'add another day
       End If
Loop

I think that will work. You can add other days like "01/01". I think you will have to play around with this since a string might strip leading zeros.

HTH


mmcdonal

Hi mmcdonal
I tried the code and it works for 12/25 but it will not work for 03/17 or other dates I put in
Thanks


Brendan Bartley

Could be a leading zero issue. Try:

sEnd = Left(dtEnd, 5)
       If sEnd = "12/25" Or sEnd = "3/17/" Then
          dtEnd = DateAdd("d", 1, dtEnd) 'add another day
       End If


mmcdonal