1. Date Calculation (A2000(SR1))

I need to calculate the difference between two dates less Saturday's and Sundays.

e.g
The difference between Monday 01/04/02 & Monday 08/04/02 will be 5 days.
Just the syntax will help me regarding nulls of course.
Dave

2. Re: Date Calculation (A2000(SR1))

Hi Dave

See here for a simple function to calculate date differences excluding weekend days.

See here for a series of much more complicated functions that also take holidays into account.

There are several threads in this Forum on workdays and holidays, e.g. those starting at <!post=Post 39610, 39610>Post 39610<!/post> and <!post=Post 67566, 67566>Post 67566<!/post> and <!post=Post 114067, 114067>Post 114067<!/post>.

Regards,
Hans

3. Re: Date Calculation (A2000(SR1))

Hans

I didn't think this was going to be difficult. This is what I have and need.
My DB is for Holiday's

The table (tblPeriod) contains 3 fields.
[Operative]-Text format
[FromDate]-Short Date format
[ThruDate]-Short Date Format

I simply need to calculate how many days each operative has had in total.
It seems post 110467 is the correct way for me, but how to implement it is the task ahead.
Can I create a pop up form with 2 fields
[Operative]
[CalculationOfDays]

Any help would be appreciated (walk thru or demo)

Dave

4. Re: Date Calculation (A2000(SR1))

Dave,

Create a new standard module. Copy the following code into it:

Function Work_Days(BegDate As Variant, EndDate As Variant) As Integer
Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer

BegDate = DateValue(BegDate)
EndDate = DateValue(EndDate)

WholeWeeks = DateDiff("w", BegDate, EndDate)
EndDays = 0
Do While DateCnt < EndDate
If WeekDay(DateCnt) <> 1 And WeekDay(DateCnt) <> 7 Then
EndDays = EndDays + 1
End If
Loop
Work_Days = WholeWeeks * 5 + EndDays
End Function

Create a query based on tblPeriod.
Add Operative to the query grid from the field list.

CalculationOfDays: Work_Days([FromDate], [ThruDate])

You can use this query as record source for a form.

Alternatively, you can put an unbound text box on a form and set the control source to

=Work_Days([FromDate], [ThruDate])

5. Re: Date Calculation (A2000(SR1))

Thanks Hans, Perfect.

Dave

6. Re: Date Calculation (A2000(SR1))

Hans

if I have 01/04/02 to 01/04/02, the query returns 0 instead of 1, can the module be modified

Dave

7. Re: Date Calculation (A2000(SR1))

Thankyou once again Hans.

This does exactly as I need.

Regards
Dave

8. Re: Date Calculation (A2000(SR1))

Dave

Originally, you stated that you wanted the number of (work) days between 01/04/02 and 08/04/02 to be 5. (Note for others: we're using dd/mm/yy format here)

From this, I inferred that you wanted to calculate differences as follows:

<table border=1><td>Monday 01/04/02</td><td>0</td><td>Tuesday 02/04/02</td><td>1</td><td>Wednesday 03/04/02</td><td>2</td><td>Thursday 04/04/02</td><td>3</td><td>Friday 05/04/02</td><td>4</td><td>Saturday 06/04/02</td><td>4 (weekend)</td><td>Sunday 07/04/02</td><td>4 (weekend)</td><td>Monday 08/04/02</td><td>5</td></table>
If you want to include the starting and ending day in the count (like the Excel function NetWorkdays), replace < by <= in the statement

While DateCnt < EndDate

Edited to correct stupid typo

9. Re: Date Calculation (A2000(SR1))

In the calculated expression field of the query, is it possible to set a constant range ie

Between Format("#01/04/" & Year(Date()) & "#") And Format("#31/05/" & Year(Date()) & "#")

This is to calculate => 01/05(current Year) and =< 31/05(current year)

This needs to stay constant so as not to re-design the query each year.

I've tried this expression but it doesn't work.

Dave

10. Re: Date Calculation (A2000(SR1))

Try the DateSerial function. It takes year, month and day (in that order) as arguments and returns a date value.

Between DateSerial(Year(Date()), 4,1) And DateSerial(Year(Date()), 5, 31)

11. Re: Date Calculation (A2000(SR1))

Sorry Hans, this is not working.

The original query works just fine.
Our holiday rota runs between 01 April and 31 May.
Our Operatives are allowed to book 20 days off other than bank holidays etc.
When the calender rolls over, the query will pick up all records instead of between the dates of the rota.

Does this help ?

Would it help having 2 unbound texts for the rota dates and setting the criteria to these instead.

Dave

12. Re: Date Calculation (A2000(SR1))

I'm sorry - I don't understand what you want.

In the post that started this thread, you wanted to calculate the number of days between two dates. The modified Work_Days function will do this. Now, I get the impression that you want to select records based on date criteria, or count them.

13. Re: Date Calculation (A2000(SR1))

Sorry to have confused the issue, it was an after thought which should have been made clear in the first instance.

Ok, I'll try more descriptively

The module and the query works ok and calculates correctly.
Now, The operatives apply their holidays in the following manner.

Mick Castrey, 01/06/02 to 14/06/02 (this is one of his holidays booked)
he may book another holiday
Mick Castrey, 15/08/02 to 18/08/02

by grouping in the query the operative name and summing the calculation , I get the correct calculation. 12 days.

Our holiday rota starts 01/04 to 31/03 the following year, so this years would be 01/04/02 to 31/03/03.

The query as it stands now, will pick up any date in the table.

So I need the query to perform the calculation in the current holiday rota.

The Module and Query we created in the first place was never wrong other than we need to set a constant in the criteria.

I tried to put this in the criteria but I think the prob is at the end of the expression, it deals with 01/04(this year) and 31/03(this year) instead of next year. I think I need a +1 somewhere.
The question is will this work in the query we created ?
Between format("#01/04/" & year(date()) & "#") and format("#31/03/" & year(date()) & "#")

Is this a bit clearer

Dave

14. Re: Date Calculation (A2000(SR1))

Dave,

Thanks for the explanation. I understand your question better now, I hope.

Try

Between DateSerial(Year(Date()), 4,1) And DateSerial(Year(Date())+1, 3, 31)

DateSerial(Year(Date()), 4,1) returns the 1st of April in the current year.
DateSerial(Year(Date())+1, 3, 31) returns the 31st of March in the next year Year(Date())+1.

Note:
Through the 31st of December, 2002, the criteria will be: between 1 April 2002 and 31 March 2003.
From the 1st of January, 2003, the criteria will be between 1 April 2003 and 31 March 2004.

15. Re: Date Calculation (A2000(SR1))

Hans
Seems like we're now on the same wave length.

Does this account for dd/MM/yy and do I put it in the criteria of the calculated field, I presumed I would.

I copied and pasted straight in, and it returned nothing when applying the query.

sorry for all the bother.

Dave

Page 1 of 2 12 Last

Posting Permissions

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