Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Knowledgebase / FAQ _ Why does the "w" not work in DateDiff() ...

Posted by: datAdrenaline Apr 30 2008, 11:48 AM

Many folks misunderstand the "w" interval setting used in the DateDiff() expression. The MS Access help file indicates that this interval is for "Weekdays". To some the term "Weekdays" means "Workdays" (ie: Monday through Friday), thus the confusion and claim that the "w" interval does not work. But the term "Weekdays" (thus the interval "w") means an indicated day of the week.
o ... with an expression like:
DateDiff("w", #4/1/2008#, #4/30/2008#)
The returned result is 4. Access looks at the start date of the indicated span and determines the day of the week the start date falls on, which is Tuesday in this case. Access will then count the number of Tuesdays between the dates indicated, note that the start date is NOT included in the count.
Other samples:
DateDiff("w", #4/2/2008#, #4/30/2008#)
-- returns 4 (the number of Wednesdays between the two dates)
DateDiff("w", #4/3/2008#, #4/16/2008#)
-- returns 1 (the number of Thursdays between the two dates)
{As a side note, the similar interval "ww" simply returns the number of Sundays between a date span.}
.....
Knowing how the "w" interval works can be quite handy when doing date calculations ...
For example, here are two archive posts that utilize the "w" interval of DateDiff():
http://www.utteraccess.com/forums/showflat.php?Cat=&Board=48&Number=1349593
http://www.utteraccess.com/forums/showflat.php?Cat=&Board=48&Number=1638262
Orealize that this little bit of information is touched on in the Access help file on the DateDiff() function, but with the confusion that is often associated around the "w" interval, I thought it was worth expanding upon. I hope you have found this information helpful. thumbup.gif