exclude weekend in Query

[neox]

I want to exclude the weekend in my datacalculation. The current is: [end date] - [begin date]. But when a weekend is passed then he counts it as a work day.. no good !!

Can anyone help me with this.

P.S. I am working with access 97... company policy

PLEASE HELP ME !

 

[Jon.N]

Exclude days of the week in a query.

In the query design grid in the field row of a new column enter

Format([Dates]![Date],"ddd")

The date field is in the dates table in my example).

In the criteria row (same column)

<>"Sat" And <>"Sun"

The table row should be blank.

 

[neox]

I don't understand !

Hi Jon.N

I am sorry but I don't know wat i have to do with this.

In the query design grid in the field row of a new column enter

Format([Dates]![Date],"ddd")

Where can I put my dates to be formatted ???

I will explain what the problem is.

I have a begintime of a troubleticket and an closing time. When I substract deze i will have the number of days that the ticket is open. The SLA that i have is 5 days a week and not seven. So I want so substract all the saterdays and sundays that is in between the start and closing time !

Can this expression of yours help me in that. My MSN email / user account is: xtremelaslo@hotmail.com

ThanX !!

Neox

 

[Jon K]

There is a function that you can use

Take a look at the function at this link:

http://www.mvps.org/access/datetime/date0006.htm
(Displaying the function here is prohibited by its Terms of Use)

After saving the function in a module, to use it is easy:

NumOfWorkDays = Work_days(BeginDate, EndDate)


The function uses the logic:
Mon Jun 17 to Tue Jun 18 = 1 work day

If what you want is "2 work days", i.e. making both dates inclusive,
you can add one line in the function code as follows:

.......
EndDate = DateValue(EndDate)
EndDate = EndDate + 1 '<--- add this line
WholeWeeks = DateDiff("w", BegDate, EndDate)

 

[neox]

Somehow it doesn't work

Ok, this doesn't seem to work... i hope that you can help me with the following. I want to know how many weekends ( sat and sun ) are in between the two dates ( begindate and enddate ) when i know that then i can substract the hours from my total time. Do you know a resolution for this problem ??

I realy hope you can !!

ThanX for all the effort.

Greets,

NEOX

 

[Jon K]

With the function Work_days() in place, you can get the number of weekend days using this formula:

NumOfWeekendDays
= TotalNumOfDays - NumOfWorkDays
= (EndDate - BeginDate + 1) - Work_days(BeginDate,EndDate)


Let's go about this using a query.

(1) Have you copied and pasted the Work_days() function from the link to a module in your database? You can save the module in any name you like. Remember to add the line in the function to make both dates inclusive.

(2) Create a table with two date/time fields. Name the fields BeginDate and EndDate. Save the table as tblNumOfWeekendDays.

Open the table. Enter a BeginDate and an EndDate.

(3) Create a new query. Copy and paste the following code to the query's SQL View:

SELECT BeginDate, EndDate,
(EndDate - BeginDate + 1) AS TotalNumOfDays,
Work_Days(BeginDate, EndDate) AS NumOfWorkDays,
TotalNumOfDays - NumOfWorkDays AS NumOfWeekendDays
FROM tblNumOfWeekendDays;

Save the query as qNumOfWeekendDays.

(4) Open the query. Edit the BeginDate and EndDate in any way you like in the query. The corresponding NumOfWeekendDays will be displayed in the last column.

Because the Work_days() function does not take care of null values of dates, if you want to use the next row in the query, you must enter two dates in the second row of the table first. Otherwise an error message will pop up.

Hope this helps.

 

[Jon.N]

RE. Exclude days of week in a query

I hope I can explain myself a bit better.

1. Create a new query and make your query the records source; i.e. the query that doesn't take out Saturday and Sunday needs to be the record source.

2. In this new query include all the fields that are in your original query. Go to to a blank column in the qery design grid and where the field name is normally entered type:

Format([Dates]![Date],"ddd")

For the criteria enter:

<>"Sat" And <>"Sun"

Now run this query It should now contain all the records of your original query except Saturdays and Sundays.

This solution is bit more low tec than the function proposed by Jon K but if you are not familiar with VBA you might want to give this idea another go.

Your calculation [end date] - [begin date] could be a calculated field in a report or form that displays the query results.

I hope this is clearer.


Jonathan

 

[neox]

Thanks for the reply

Hi John.N

Thank you for your reply, but i found an even simpler awser. Check this out, without Vb scripting.

When you Substract the enddate from the begindate you will have the number of day open. Then you devide this number bij 7, now you have the number of weeks open. Round this number by 0,5. You will now have a accurate weekendindication

When you multiply this number ( rounded number ) by 2 you will have the total weekend days ( sat and sun ). Substract this from your first days open and you will have you total day open -/- weekend.

Open time: ( enddate - begindate )
Open weeks: Open time / 7
Round Open weeks: round (open time;0)
Open time -/- weekends: Open time - (Round open weeks*2)

For example:
Bdate = 18-06-02
Edate = 24-06-02
Opentime = 6
Open weeks = 6/7 = 0,85
Rounded Open Weeks = 1
Open time -/- weekends: 6 - ( 1*2 ) = 4 days = Working Days.

Check it it realy works !!

 

[simongallop]

Not sure that your solution does work! Admitedly I am not sure how your weekend calc thing works, but how does it differentiate between the following examples?

Here are 2 examples:

SDate: 17th June 2002 --> EDate 21st June 2002
SDate: 21st June 2002 --> EDate 25th June 2002

Both examples are 5 days long yet the second one is actualy 3 days due to the weekend.

Better way is to look at the weekday value of the dates and use the following formula in your query:

WorkingDays: IIf(Weekday(StartDate) > Weekday(EndDate),1+Int((EndDate-StartDate)/7),Int((EndDate-StartDate)/7))*5 + Weekday(EndDate) - Weekday(StartDate) +1

The +1 is becauseyou are including the first day ie 17th -17th = 1day

HTH