Weekdays between dates - DateDiff() function 2

[BibleMan]

All:

I am trying to create a calculated field on a Form that will calc the number of Weekdays between an existing field "[Letter 1 Sent]" and today.

I have written the following statement and the results returned are incorrect. When I substicute "d" for "w" in the first argument, it DOES calculate the correct number of actual days, but what I need is the number of weekdays.

=DateDiff("w",[Letter 1 Sent],Now(),2,0)

Am I missing something simple and stupid?

Thanks for your assistance, as always

 

[SgtJarrow]

See MichaelRed's faq705-3213

****************************
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[BibleMan]

Robert:

I appreciate your quick response. I had reviewed the FAQ you referenced, as well as performing a search of all forums for the answer before I posted.

The FAQ referenced is much larger a solution than I was looking for - kinda like driving finishing nails with a pneumatic nailer. :^)

I believe I can create a calculated field with a simple DateDiff statement. My question was why the statement as I wrote it was not working.

Can you, or anyone else, help?

Thanks so much.

Joe

 

[glalsop]

Hi joebren -

You do need to use the "d" argument instead of the "w" argument. Check out the following link, as it should have all the code you need:

http://support.microsoft.com/default.aspx?scid=kb;en-us;207795&Product=acc2000

-Gary

 

[SgtJarrow]

You can't just use datediff....this is because datediff does not know how to determine a working day or not. While you can begin with datediff, it will take a bit more. You COULD use datediff to walk through each between the first and last, and if not sat or sun add one to a counter...when done your counter should equal your work days...but as MichaelRed's FAQ and my own work days code I have created, using this method also will not count for holidays and non-working days...

For example, if I wanted the number of work days between 11/25 and 12/2, using datediff gives me 8 days. DateDiff looped thorugh each day and checking for sat or sun gives me six days....but if I use MIchaelRed's well developed FAQ or my own workdays code I created I get 4 days....the actual correct number becuase 11/27 was a holiday, my company had 11/28 off also and then 11/29 and 11/20 were weekend days...

So, while datediff may be a starting point, I hope you can see why it is not correct. Once you create the setup proposed by MichaelRed's FAQ (or I can provide my code if you want it), it is simply a single line call anywhere in your database to get the calculations....

****************************
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[glalsop]

mstrmage1768,

In BibleMan's original post he simply asked for the number of weekdays, not the number of workdays. That is the link I provided, and that is much simpler than what MichaelRed proposes in his FAQ. However, as you say, it is not very useful for day-to-day business applications.

Perhaps you wouldn't mind posting your code for determining workdays, or e-mail the procedure?

glalsop@earthlink.net

Thanks,

-Gary

 

[SgtJarrow]

glalsop....

Sorry....you are correct in that the number of weekdays was asked for.....but I took that to mean working days....

And mail inbound with my workdays code....

****************************
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[MichaelRed]

Of course programmers are 'free' to do as they please so I'm just as happy for your use of any approach you choose. The faq was developed specifically for counting workdays, after several forays into the 'simpler' soloutions which abound and universally getting 'flak' from the world of users. Their universal issue (question) is -if you can exclude these days (week end days) wht can't you also exclude the holidays.

In some cases, I thought the concept was just guilding the lilly, but it can make a significant difference in (for example) calculating a payout (overtime rate, or in schedualing a work effort).

In the end, it doesn't matter to me, wheather it is necessary of wishful, once developed the procedure doesn't taks so long to run that it is an impact to the execution of any app (program) I have ever developed, and -as a PROGRAMMER- I always decline to do the maintenance of the holiday table, but will provide a simple data manipulation form to ease that chore.

I do not think that it is such a 'large' process, but more like teaching someone how to drive the nail - regardless of size as opposed to driving the specific nail for them (like the parable of the fishes?).







MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[SgtJarrow]

Hey MichaelRed,

Since you mention not wanting to manually update your Holiday tables....I started on some code, posted below, that will do most of it for you....the code is correct.

There are two basic holiday types...fixed date, such as New Years and Veterans Day, and flotaing holidays, such as Thanksgiving. What I did some time agao, was write this code, then did a Google search and found out which holidays were fixed and what holidays were floating and how they were determined. I plugged these holiday properties into a table and fed them through the code as appropriate and viola...I had accurate dates for all holidays. I checked these calculations for all dates between 1990 2020...and they were all correct.

Easter was some freakish calculation...but I found the calculation somewhere and just fed Easter in separately...I don't have the bit of code that did the actual saving of the holiday dates into the final table, but you should be able to make it....let me know.




Public Function FixedHoliday(intYear As Integer, intMonth As Integer, intDay As Integer) As Date

FixedHoliday = DateSerial(intYear, intMonth, intDay)

End Function

Public Function FloatingHoliday(intYear As Integer, intMonth As Integer, intWeek As Integer, _
intDayOfWeek As Integer) As Date

FloatingHoliday = DateSerial(intYear, intMonth, (8 - WeekDay(DateSerial(intYear, intMonth, 1), _
(intDayOfWeek + 1) Mod 8)) + ((intWeek - 1) * 7))

End Function

Public Function WeekDaysPerMonth(intYear As Integer, intMonth As Integer, _
intDayOfWeek As Integer) As Integer

Dim i As Integer
Dim intTotalDays As Integer
Dim intWeekDayCount As Integer

intWeekDayCount = 0
intTotalDays = Day(DateAdd("d", -1, DateSerial(intYear, intMonth + 1, 1)))

For i = 1 To intTotalDays
If WeekDay(DateSerial(intYear, intMonth, i)) = intDayOfWeek Then
intWeekDayCount = intWeekDayCount + 1
End If
Next i

WeekDaysPerMonth = intWeekDayCount

End Function

Public Function EasterDate(intYear As Integer) As Date

Dim i As Integer

i = (((255 - 11 * (intYear Mod 19)) - 21) Mod 30) + 21

EasterDate = DateSerial(intYear, 3, 1) + i + (i > 48) + 6 - _
((intYear + intYear \ 4 + i + (i > 48) + 1) Mod 7)

End Function

****************************
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[glalsop]

Hi Robert -

Very good stuff... Have a star!

If anyone else is interested, I did a quick search for the 'rules' you would need to use Robert's code, and here are a couple of hits:

http://familyinternet.about.com/library/blholidaydates.htm

http://www.smart.net/~mmontes/ushols.html

 

[SgtJarrow]

glalsop,

Thanks....and FYI, the code I sent you is now available through faq705-4531

****************************
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[MichaelRed]

Robert,

Actually Win (all versions since at least '98) includes a file of 'official' dates for more holidays than anyone could ever include in the table. Alas, alack and awry, no place that I have ever been asked to implement the functionallity has ever observed ALL of the holidays on the official dates. I have also done the lookup for the code to determine the official dates, only to discard them. As soon as one holiday is observed on some date other than the official one, the general process become subject to SOME manual massage, and it just causes confusion to have to enter / correct / deal with the exceptions, so I just set it up as a manual data entry / edit and have the boss have some data entry personnel become responsible. I have also had organizations where different parts of the org observed different holidays and even observed the dsame holiday on different dates. Simply to much effort to keep track of (at least for tired OLD gery matter boxes). Even worse (although this didn't occur in my term of employment) one company even changed the holidays they observed.

My lookup for the holiday date calculation found discrete calculations for all of the floating holidays for every organization I needed, at least up to the point I quit even attempting that approach. And, of course, the fixed ones are easy, so all opne needs is a single procedure which includes the fixed holidays mopnth and day as well and have that one also call the specific ones for the floating holidays, so your approach is quite a bit more sophisticated than what I was attempting. But even with the additional sophistication, I don't see how to (reasonably) move President's Day to an arbitrary day between Christmas and New Years (an actual requirement).



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[b1kerch1ck]

Hi,
I am using Access 2003 and it has the same bug: "w" returns weeks not weekdays with DateDiff.

I tried using "DateDiffW" as recommended in the Knowledge Base article linked above, but it returns an error.

I am frustrated because
1) The version of Access I'm using is newer than what the Knowledge Base article refers to, but the bug is still there, the help file has not been updated (still says "w" will return weekdays), and anyway, the Knowledge Base article gives incorrect information because DateDiffW doesn't work!

I am simply trying to have a "days left until" unbound text box at the bottom of a report and don't want to write a whole bunch of VBA code.

Am I missing something? Is there now a function in Access 2003 for calculating weekdays between two dates without coding the whole thing?

Thanks

 

[PHV]

b1kerch1ck, the code for the DateDiffW function is given in the KB article !

 

[b1kerch1ck]

Thanks PHV,

I saw it but I was just hoping that the dateDiff function had been fixed by Microsoft.

I will use the code or just skip the text box.

Thanks for writing back

 

[MichaelRed]

[qoute b1kerchick] " ... Access 2003 and it has the same bug ... "[/qoute]

It is NOT a bug. It is the documented behaviour, and has been so for several generations (versions)n of VB. While you man prefer other implementations, it is hardly reasonable to refer to the documented aspects of intrinsic functions as "Bug"gy. In the documentation, it does refer to "weekdays", however the last time I checked, Saturday and Sunday were considered part of the collection of the (SEVEN) says of the week?

While I'm not the biggest fan of narrow deffinition technical documentation, I can at least TRY to read it in the venue it is intended for, which is NOT a casual glance at the headline and storming through the implementation w/o consideration of the remainder of the information (examples and at least a brief test of the part which is critical to what I'm trying to accomplish).





MichaelRed