Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Apr 2003
    Location
    Morgantown, Pennsylvania, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DateDiff Function (XP, 2003)

    I did a search and found that if I wanted to calculate the number of weekdays (workdays) between two dates, I should use a procedure found on the The Access Web page.

    Does this mean that I'm not crazy, and that the "w" option on the DateDiff function doesn't work? <img src=/S/surrender.gif border=0 alt=surrender width=31 height=23>

    I've tried to calculate the difference (in weekdays), between two date fields in a table. The "d" will give me days, the "ww" will give me weeks, and so on, but the "w", purported to be weekdays, returns a 0. Can anyone else make this option work?

    Here's the code in my query:

    difference: DateDiff("w",[dtmOrdDate],[dtmDispatchDate],1,1)

    Thanks for any help provided.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DateDiff Function (XP, 2003)

    From Microsoft:
    <hr>When interval is Weekday ("w"), DateDiff returns the number of weeks between the two dates. If date1 falls on a Monday, DateDiff counts the number of Mondays until date2. It counts date2 but not date1. If interval is Week ("ww"), however, the DateDiff function returns the number of calendar weeks between the two dates. It counts the number of Sundays between date1 and date2. DateDiff counts date2 if it falls on a Sunday; but it doesn't count date1, even if it does fall on a Sunday.
    ...
    The firstdayofweek argument affects calculations that use the "w" and "ww" interval symbols.<hr>
    So neither counts the number of working days. You need code such as from the Access web for that.

  3. #3
    Star Lounger
    Join Date
    Apr 2003
    Location
    Morgantown, Pennsylvania, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DateDiff Function (XP, 2003)

    Thanks Hans, for your quick reply.

    But I will say this to all those Microsoft people listening in....

    I sure need to count the number of weekdays (workdays) between two dates a lot more often than the number of times I need to know how many Tuesdays fall between two dates. Geez, I can get that by subtracting one date from another and dividing by 7 inside an integer function.
    difference2: Int(([dtmdispatchdate]-[dtmorddate])/7)

    How about we upgrade the next version of Access with a weekday (workday) option in the datediff function? <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> <img src=/S/fanfare.gif border=0 alt=fanfare width=31 height=23>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DateDiff Function (XP, 2003)

    DateDiff is not a specific Access function, it is general VBA.

    You could send an email to mswish@microsoft.com. Explain clearly but briefly what you would like, and why you need it.

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: DateDiff Function (XP, 2003)

    I've come not to rely on the DateDiff function too much, because it seldom returns what one expects; or at least it doesn't return what you think it should! Basically, how DateDiff looks at a date (and the difference between dates) is based on the interval you specified. For example. if you specified "yyyy" to get difference in years, it only looks at the year part of the 2 dates, and subtracts to find the difference. So the difference between 1/1/05 and 12/31/05 is 0! While the difference between 12/31/05 and 1/1/06 is 1!
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    Star Lounger
    Join Date
    Nov 2005
    Location
    Edmonton, Alberta, Canada
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DateDiff Function (XP, 2003)

    I agree for the most part (the only thing I ever ask datediff for are the days between two dates and then for any other units I do my own calculations). However, there would be a "gotcha" in calculating the number of workdays - what about holidays? Since Access is not confined to the US, it wouldn't be practical for datediff to take holidays into account, reducing the utility of automatically calculating the number of workdays between two dates.

  7. #7
    Star Lounger
    Join Date
    Apr 2003
    Location
    Morgantown, Pennsylvania, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DateDiff Function (XP, 2003)

    Hello,

    OK, I decided to go searching for some resources other than the Access Web, or create my own. I found out a couple of things about calculating the number of weekdays between weekdays:

    1) As long as the beginning and ending dates are weekdays, it ranks as easy.

    2) if you want to allow for any possibility in a day, it ranks as medium difficult.

    3) If you want to exclude holidays, it ranks as difficult.

    4) I had to decide whether Monday to Friday counted as 5 days or 4. Most date calculations will come out as 4, but I wanted it to be 5. So I added 1 to the calculation to include both ends of the range.

    Since I am working on a database that only has weekdays as beginning and ending dates (since the company is not doing business on Saturdays and Sundays), I took the easy solution. I created a formula in Excel, and it looks like this:

    =IF(WEEKNUM(B3)=WEEKNUM(C3)-1, C3-B3-1,(C3-B3+1)-(INT((C3-B3+1)/7)*2) )

    where cell B3 contains the beginning date and cell C3 contains the ending date.

    For Access, I "borrowed" a function from Crystal Reports called cdDateDiffSkipWeekends. I had to change the references to constants, as Crystal Reports refers to constants as crConstant and VBA uses vbConstant. Also, Crystal has a dateTime data type, which needed to be converted to Date for my purposes. So after a quick conversion. the Access function looks like this:

    Function cdDateDiffSkipWeekends(startDateTime As Date, endDateTime As Date) As Integer

    'this function assumes that weekends are on Saturday and Sunday. You can modify the constants
    'below to change this assumption.
    cdDateDiffSkipWeekends = _
    DateDiff("d", startDateTime, endDateTime) - _
    DateDiff("ww", startDateTime, endDateTime, vbSaturday) - _
    DateDiff("ww", startDateTime, endDateTime, vbSunday)

    End Function

    Again, both work all the time if the beginning and ending dates are weekdays. And since we are calling this the Weekdays between Weekdays function, I am ignoring holidays. I guess if I called it Workdays, I would have to account for holidays.

    Again, thanks for all the help. I recommend the Lounge to all the Access developers I run into.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DateDiff Function (XP, 2003)

    The WORKDAY function in Excel's Analysis ToolPak add-in, and custom Access functions such as those found in Date/Time: Doing WorkDay Math in VBA on the Access Web, use an optional argument for specifying holidays, so they can easily be adapted for use in other countries.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •