Calculate Working Days inc. Staff Start & Finish Dates

[andy_dyer]

Hi everyone...

I've got the following code that I've found on this forum to calculate my working days between two selected dates:

Function CalcWkDays2(dteStartDate As Date, dteEndDate As Date, _
YCnt As Boolean, Optional pExcl As String = "17") As Integer
'*****************************************************
'Name:      CalcWkDays2 (Function)
'Purpose:   Count # of days between two dates, with
'           options to:
'           (1) Include or exclude the start date in count
'           (2) Specify weekdays to exclude (default
'               set to Saturday (7) & Sunday (1).  To exclude
'               Tuesday (3) & Thursday (5), specify "35".
'Parameters:    dteStartDate & dteEndDate formatted as dates
'               YCnt:   Specify True to include start date in
'                       count, False to exclude it.
'               pExcl:  Weekdays to exclude
'Inputs:    From debug window:
'           (1) ? CalcWkDays2(#01/01/01#, #07/01/01#, True)
'           (2) ? CalcWkDays2(#01/01/01#, #07/01/01#, False)
'           (3) ? CalcWkDays2(#01/01/01#, #07/01/01#, True, "")
'           (4) ? CalcWkDays2(#01/01/01#, #07/01/01#, False,"")
'           (5) ? CalcWkDays2(#07/01/01#, #01/01/01#, False,"")
'           (6) ? CalcWkDays2(#01/01/01#, #07/01/01#, False,"23456")
'Output:    (1) 130; (2) 129; (3) 182; (4) 181; (5) -181; (6) 52
'
'*****************************************************

Dim n As Integer, wdays As String, datehold As Date, dteFlag As Boolean

dteFlag = False
'Reverse the dates if they were input backwards
If dteStartDate > dteEndDate Then
   datehold = dteStartDate
   dteStartDate = dteEndDate
   dteEndDate = datehold
   dteFlag = True
End If
n = 0
dteStartDate = dteStartDate - Not (YCnt)
'days to exclude (7 & 1 unless other specified)
wdays = pExcl

Do While dteStartDate <= dteEndDate
   n = n + IIf(InStr(wdays, Weekday(dteStartDate)) = 0, 1, 0)
   dteStartDate = dteStartDate + 1
Loop
'return negative value if the Start Date
'was initially greater than the End Date
CalcWkDays2 = n * IIf(dteFlag, -1, 1)
End Function

What I also need to take into account is the start and finish dates for individual staff members...

So - I need to check the working days between two input dates for a member of staff unless the member of staff started after the calculation start date ([forms![frmquery]![txtStart]) or finished before the calculation finish date ([forms![frmquery]![txtFinish])

In have a staff table (tblStaff) with StaffID, User Name, Department Name, User Start Date, User End Date...

Is there a way I can get my code to check the start and end dates and subsititue in instead of the input dates for these members of staff when making the calculation?

I'll then need a way of rolling this up so I get a individual total and then a department total... but one step at a time...

I hope I've explained that properly - many thanks for anyone's help??

 

[DCrake]

You would need to add a further two date parameters to the function when calling it

Function CalcWkDays2(dteStartDate As Date, dteEndDate As Date, _
YCnt As Boolean, Optional pExcl As String = "17", [B]dtmEmpStart As Date, dtmEmpEnd As Date[/B]) As Integer

Then do a comparision to see if the dtmEmpStart is after the dteStartDate. If so, replace steStartDate with dtmEmpStart. Likewise do the same for the end dates.

This should then give you a more accurate answer

This is all aircode and untested.


David

 

[andy_dyer]

Thanks for coming back to me - I'm 4 years rusty with any vba so have literally cut and paste and slightly tweaked the code I quoted...

If you could explain in any more simple terms what I should be looking at with the air-code you suggested I'd be very grateful...

Thanks ever so much...

 

[DCrake]

In a nutshell, you want to calculate the number of working days an employee working during a given period.

Lets say the period is 01/01/2009 to 01/03/2009 For anyone who has been employed during the full period they will all have the same figure. Now lets say one person only started on the 01/02/2009 they will only have worked for 2 of the 3 months, like wise if someone left on the 01/02/2009 they to will only 1 months working days.

So for each time the function is run against an employee it needs to know 4 dates

Function CalcWkDays2(dteStartDate As Date, dteEndDate As Date, _
YCnt As Boolean, Optional pExcl As String = "17", dtmEmpStart As Date, dtmEmpEnd As Date) As Integer

dteStartDate = first date in your range
dteEndDate = last date in your range
dtmEmpStart = date employee began working for you
dtmEmpEnd = date the employee left the company (if they have not left the company this will ba blank/null

So what we first need to do is to look if the employee start date is leter than the range start date, if it is make the range start date the employee start date.

Next we do the same for the employee end date, But only if there is a end date. If there is no end date then retain the end range date.

So now we have the correct date range to work with so we can now calculate the working days for the employee

Does that make more sense?

David

 

[andy_dyer]

I'm sorry... I'm having a complete brain lapse with regard this...

I've got no idea how to begin calling this function and getting it to pick up the correct date fields for the different locations...

dteStartDate = [forms]![frmQuery]![txtStart]
dteEndDate = [forms]![frmQuery]![txtFinish]
dtmEmpStart = [tables]![tblStaff]![Start Date]
dtmEmpEnd = [tables]![tblStaff]![End Date]

Trying hard to not come across completely clueless but I've never done anything as complicated as this before... (most code simply replacing macros)

If you can offer any more help short of coming to Sheffield and sitting at my desk I really would be gratfeul - sorry for being a pain!

 

[DCrake]

Right here goes

Make sure you have saved your function (CalcWkDays2) in a module

Next press Ctrl+G to enter the immediate window


Type in:
? CalcWkDays2(#01/01/2009#,#01/04/2009#,-1,"17",#01/02/2009#,#01/03/2009#)

We are passing 6 parameters to the function

1. 01/01/2009 - first date in your range
2. 01/04/2009 - last date in your range
3. -1 (true) - To include the date in 1.
4. 17 - exclude days 1 and 7 from the counter (Sunday & Saturday)
5. 01/02/2009 - Employee start date
6. 01/03/2009 - employee end date

Using the above parameters if we simply counted the number of all days between the given period of choice it would return 90

If we asked for working days it would return 64 days

But the employee only worked for us from 1st Feb to the 1st Mar. So 64 would be wrong. The function checks to see if the date in (5) is greater than the date in (1) if it is it changes the start date to be (5).

It then check to see if the date in (6) is less than the date in (2) if it is then use the date in (6) as the end date. Therefore the range is now 01/02/2009 to 01/03/2009.

It then calculates the working dates between the revised dates which would return 20 days.

So now that has been explained lets see how we use this.

Create a new query and pick you employee table.
Include the following fields in the query
[EmployeeStartDate]
[EmployeeEndDate]

Next add a new column call WorkingDays

WorkingDays:CalcWkDays2(#01/01/2009#,#01/04/2009#,-1,"17",[EmployeeStartDate],Nz([EmployeeEndDate,#01/04/2009#))

I have used the Nz() function to replace any null values with the last date in your range.

Save your query and run it. It should now give you the number of working days for each employee during the chosen period. You should check that at least one employee has differing dates form the parameters given.

Where I have used [EmployeeStartDate] and [EmployeeEndDate] these names should be the field names in your table.

If you are still struggling after this thread send a snippet of your mdb to test on.

David

 

[andy_dyer]

Thank you ever so much for your help...

I've used this wording but get an error:

WorkingDays: CalcWkDays2(#01/01/2009#,#01/04/2009#,-1,"17",[User Start Date],Nz([User End Date],#01/04/2009#))

"The expression you entered has a function containing the wrong number of arguments."

I'm trying to create a template version of my db so I can attach...

 

[andy_dyer]

hope this helps...

 

[DCrake]

I have made changes to your mdb to get it to work.

You need to look at the FrmQuery first to see how the dates from the form are passed to the new queries.

The query that calculates the working days is named QryWorkingDayByEmployee. There is also a summary of the above named QryWorkingDaysSummary

Also look in the module that contains the function to see how it works.


Regards
David

 

[raskew]

Hi -

If you had a table that looked like this (note that my dates
are in US Short-Date format)

EmployeeID	StartDate	EndDate
1	          1/21/2009	4/15/09
2	         12/16/2007

...and you desired to return the number of work days
(Saturday and Sunday excluded) between 01-Jan-2009
and 01-Mar-2009, you could invoke this query:

SELECT
    EmployeeID
  , IIf([StartDate]<#1/1/2009#,#1/1/2009#,[StartDate]) AS xStartDate
  , Switch(IsNull([EndDate])
  , #3/1/2009#,[EndDate]>#3/1/2009#
  , #3/1/2009#,True,[EndDate]) AS xEndDate
  , CalcWkDays2([xStartDate]
  , [xEndDate],True,"17") AS TotWkDays
FROM
   tblWkDaysTest;

...which would return:

EmployeeID	xStartDate	xEndDate	TotWkDays
1	        1/21/2009	3/1/2009	28
2	         1/1/2009	3/1/2009	42

HTH - Bob

 

[raskew]

To expand on my previous post--If you wanted to prompt the user for the desired Start and End Dates, this parameter query:

PARAMETERS EnterStartDate DateTime, EnterEndDate DateTime;
SELECT
    EmployeeID
  , IIf([StartDate]<EnterStartDate,EnterStartDate,[StartDate]) AS xStartDate
  , Switch(IsNull([EndDate]),EnterEndDate,[EndDate]>EnterEndDate,EnterEndDate,True,[EndDate]) AS xEndDate
  , CalcWkDays2([xStartDate],[xEndDate],True,"17") AS TotWkDays
FROM
   tblWkDaysTest

;

...would return:

EmployeeID	xStartDate	xEndDate	TotWkDays
1	        1/21/2009	3/1/2009	28
2	        1/1/2009	3/1/2009	42

Bob

 

[andy_dyer]

Thanks David for your help and also Bob for your suggestion...

For the returned database I am very grateful!

One question:

At present the new query "QryWorkingDaysByEmployee" is picking all employees from tblStaff.

For my individual report I'll need it to just pick the person chosen from the field txtStaff on frmQuery...

For my department report I'll need to it to only select the staff from the department(s) selected in my list box lstDept...

Is there a way I can get these to feed through?

Thank you so much for your time!

 

[andy_dyer]

I've also just been told that we are to recruit our first part time employee...

I'm guessing I'll need to add a field to the tblStaff for FTE i.e. 0.8 for 4 days a week or something similar and somehow include that into these calculations?

Thanks again for your time and support

 

[DCrake]

Yes you will. Assign 1 to each employee as their WHE then for any part timers give them a proportionate value. Then at the end of the field in your query

WorkDays:CalcWkDays2(...)*(7.5*WHE) to get the actual hours in their contract. anyway do you not store the contracted hours as a field in your staff table?

If you can follow how I created the functions to get the date range you can replicate the functionality for the employee's and Departments



Dim LngDepartment as long

Function GetDepartment() as Long
GetDepartment = LngDepartment
End Function


Then in your query use GetDepartment() in the condition row.


David

 

[andy_dyer]

Thank you so much! I've got the whole thing including part time working for individuals passing the query "QryWorkingDaysByEmployee" into another query and filtering where the staff name matches the text box on the frmQuery...

I can't get the same query trying to look up the selected departments in the list box to work the same?

Have you any ideas how I can get this part to work - I feel like I'm so close!

Thanks again for your help and time

 

[andy_dyer]

Ok...

I've got a query that claculates all staff working days between two dates taking into account start and finish dates and also their FTE...

SELECT tblStaff.[User Name], tblStaff.[Department Name], tblStaff.[User Start Date], tblStaff.[User End Date], calcwkdays2(GetDateLower(),GetDateUpper(),True,"17",[User Start Date],nz([User End Date],GetDateUpper())) AS WorkingDays, [WorkingDays]*7.5 AS WorkingHours, tblStaff.FTE, [WorkingHours]*[FTE] AS [Total Working Hours]
FROM tblStaff;

I've got another query that pulls all data from my main table based on the departments selected in a multi-select list box...

SELECT *
FROM tblData
WHERE (((tblData.[Department Name]) In ('2 Consultancy','10 Project Management')));

How can I combine these two together so I get all of the information in the first query but only for the staff in departments chosen in my listbox??

This is my last challenege before being able to present my database to the company on Wednesday...

Can anyone help??

 

[DCrake]

Createa third query and bring in the other two queries and join them by the department name as you do not have the department id in either query.
Recommended.

SELECT tblStaff.[User Name], [B]tblStaff.[Department Name], [/B]tblStaff.[User Start Date], tblStaff.[User End Date], calcwkdays2(GetDateLower(),GetDateUpper(),True,"17 ",[User Start Date],nz([User End Date],GetDateUpper())) AS WorkingDays, [WorkingDays]*7.5 AS WorkingHours, tblStaff.FTE, [WorkingHours]*[FTE] AS [Total Working Hours]
FROM tblStaff;

I've got another query that pulls all data from my main table based on the departments selected in a multi-select list box...

SELECT *
FROM tblData
WHERE ((([B]tblData.[Department Name]) [/B]In ('2 Consultancy','10 Project Management')));

 

[andy_dyer]

I do remember the theory... but it's been several years since I used Access where I needed to link tables and develop relationships between my data...

Still a bit rusty... but with my manager getting very excited about this database I think i'll be dusting off my long lost memory cells...

Thank you for your help - once I'd played around with the direction of the relationship this did what i needed it to...

Thank you ever so much for your patience!!

 

[DCrake]

Have you got it all working now? Also is this thread related to your other thread?

http://www.access-programmers.co.uk/forums/showthread.php?t=168187

If so let me know and I will close it.

David

 

[andy_dyer]

Hi - yes this bit is working - my other post about discretionary time is still causign me problems and I need that one leaving open to see if anyone can help!

But thank you ever so much for your help with this thread!