Need help with Workings Days Count 1

[RobinHood786]

Hi all,

I’ve been using Michael Red's faq181-261 'Calculate working days between two dates working days' function for a while and it’s proved to be a very useful tool, so can I firstly say thank you to Michael Red for posting his solution.

Using this model I’ve created a sort of makeshift calendar that shows the number of working days alongside the actual day count so users can ensure they’re not booking appointments on non working days. Here’s the SQL from the underlying query of the form.

SELECT Date()+[TblDays]![Days] AS FullDate, TblDays.Days, DeltaDays(Date(),[FullDate]) AS [Working Days]
FROM TblDays
ORDER BY TblDays.Days;

TblDays.Day is purely a count of days (0,1,2,3…. ) that the user can manage and thereby reduce or increasing the calendar period.

I’m having a few problems and would appreciate some help or guidance.

In this example my holidays are;

HoliDate HoliName
07/06/2007 Test 1
20/06/2007 Test 2
21/06/2007 Test 3
10/07/2007 Test 4
11/07/2007 Test 5

The results I receive after running the query for 41 days (0 + 40) are shown below. (Date() is 06/06/2007)

FullDate Days Working Days
Wed, 6 Jun, 2007 0 1
Thu, 7 Jun, 2007 1 2
Fri, 8 Jun, 2007 2 3
Sat, 9 Jun, 2007 3 3
Sun, 10 Jun, 2007 4 3
Mon, 11 Jun, 2007 5 4
Tue, 12 Jun, 2007 6 5
Wed, 13 Jun, 2007 7 6
Thu, 14 Jun, 2007 8 7
Fri, 15 Jun, 2007 9 8
Sat, 16 Jun, 2007 10 8
Sun, 17 Jun, 2007 11 8
Mon, 18 Jun, 2007 12 9
Tue, 19 Jun, 2007 13 10
Wed, 20 Jun, 2007 14 10
Thu, 21 Jun, 2007 15 10
Fri, 22 Jun, 2007 16 11
Sat, 23 Jun, 2007 17 11
Sun, 24 Jun, 2007 18 11
Mon, 25 Jun, 2007 19 12
Tue, 26 Jun, 2007 20 13
Wed, 27 Jun, 2007 21 14
Thu, 28 Jun, 2007 22 15
Fri, 29 Jun, 2007 23 16
Sat, 30 Jun, 2007 24 16
Sun, 1 Jul, 2007 25 16
Mon, 2 Jul, 2007 26 17
Tue, 3 Jul, 2007 27 18
Wed, 4 Jul, 2007 28 19
Thu, 5 Jul, 2007 29 20
Fri, 6 Jul, 2007 30 20
Sat, 7 Jul, 2007 31 20
Sun, 8 Jul, 2007 32 20
Mon, 9 Jul, 2007 33 21
Tue, 10 Jul, 2007 34 22
Wed, 11 Jul, 2007 35 23
Thu, 12 Jul, 2007 36 24
Fri, 13 Jul, 2007 37 25
Sat, 14 Jul, 2007 38 25
Sun, 15 Jul, 2007 39 25
Mon, 16 Jul, 2007 40 26


As you can see I’m not quite getting the desired results in some instances.

Firstly, does anybody have suggestions as to how I can remedy the above?

Secondly, how would I amend Michael's code so that that current date is not included in the working day count. In the above example that would mean Wed 6 June 2007 would be 0 workings Days.

I hope I've explaied my queries subut please

Hope I’ve explained my questions sufficiently well but let me know if further clarification is needed.

Thanks in advance for any assistance.


Regards

 

[MichaelRed]

please be more specific re " ... not quite getting the desired resiults ... " e.g. what date / day count is not according to the 'desire'?

I would probably not attempt to modify the procedure, but do the minor additional math in your SQL (just subtract it in the expression)

MichaelRed

 

[RobinHood786]

Hi Michael Red,

Thanks for your reply. Nice to speak to very man who wrote the whole sherbang in first place.

According to the dates in my holiday table

07/06/2007
20/06/2007
21/06/2007
10/07/2007
11/07/2007

are all holidays

So 7 June 2007 shouldn’t be counted as a working day. However, as you can see from the results (a bit skewed I now) it’s included and shown as the second working day. Then the 20th and 21st are both as expected and not shown as working days, but the 10th and 11th of July 07 are then respectively shown as the 22nd and 23rd working day when I was expecting them to have not been counted as such.

I did try subtracting from the date in my query but it threw the count out. It was a while ago so I can’t recall exactly the results, but I’ll post this as a separate post/issue once I get the above in order. In any case that part’s not so important as I can manipulate the results later, I was just hoping you’d be able to show me a more elegant way of doing so.

The bit’s that really got me all worked up and beating my head is the working day count.

 

[PHV]

The more likely is a mix of dd/mm/yyyy vs mm/dd/yyyy strings comparaisons.
Bottom line: always use non ambiguous format like yyyy-mm-dd

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[RobinHood786]

Thanks PHV

I gave your idea a run but but got the same results, nice call though, and I take your point about the formats.

 

[MichaelRed]

as PHV notes, date formats are often an issue. in ALL Ms. A. expressions, the date format MUST be the "U.S." version - regardless of local settings. This is particularly important in calling functions, so if your locale setting is NOT U.S. and you are getting dates from a recordset, they need to be re-cast (formatted) to the U.S. m-d-y arrangement.

Seems silly, after all the internal storage is just bore unto a double than anything resembling a caklendar expression.



MichaelRed

 

[RobinHood786]

Thanks PHV

I went back and at looked at MichealRed's module, and your spot-on about changing the format to yyyy,mm,dd

An even better call then I first thought.

Many thanks