Question Due date - Working Days (1 Viewer)

[Caspius]

Hello.

I'm trying to work out what date would be 20 working days from a inputted date. So far I've got a table with the start date [startdate] and a holiday table. I'm using Access 2010.

I know that to work this out normally would be DATEADD (day,20, startdate)

There seems to be plenty of guides/code on how to work out the number of working days between two dates but I can't seem to find a anything about a due date. There must be something already existing out there.

Thanks

 

[sparks80]

Hi,

There is an Excel function that will do this for you. You can create an Access VBA function using it like this:

Firstly create a new VBA module. Next you will need to add a reference to Excel. Go to the VBA editor (press Alt + F11) and select Tools > References from the menu. Scroll down the list and select Microsoft Excel.

Then enter the following code into the new module:

Public Function WorkingDay(StartDate As Date, NumberOfDays As Integer) As Date
    WorkingDay = Excel.WorksheetFunction.WorkDay(StartDate, NumberOfDays)
End Function

The function should then be available to your access query. Apologies as I haven't tested this.

 

[RainLover]

There seems to be plenty of guides/code on how to work out the number of working days between two dates but I can't seem to find a anything about a due date. There must be something already existing out there.

So what is the difference between "Two Dates" to "One Date and Due Date."

 

[smig]

I think you can write your oun function for this.
It shouldn't be too difficult.

What you should do is like this:
Add 1 day, if this is a non working day add another day - you can check against both weekend days and holidays table, and do the test again...
loop this untill you add the number of days you want.

function fnWorkingDays(StartDate as date, NumberOfDays as long) as date
dim i as long
dim x as long
 
x = 0
 
for i = 1 to NumberOfDays
TestAgain:
x = x + 1
if fnCheckWeekendDays(DateAdd("d", StartDate, x)) = True OR fnCheckHolidays(DateAdd("d", StartDate, x)) = True Then
  goto TestAgain
end if
Next i
fnWorkingDays = DateAdd("d", StartDate, x)
end function
 
function fnCheckWeekendDays(chkDate as Data) as boolean
... Check here for weekends
end function
 
function fnCheckHolidays(chkDate as Data) as boolean
... Check here for holidays
end function

 

[Caspius]

There seems to be plenty of guides/code on how to work out the number of working days between two dates but I can't seem to find a anything about a due date. There must be something already existing out there.

So what is the difference between "Two Dates" to "One Date and Due Date."

'Two dates' is start date and end date and just working out the number of days between them. 'Due date' is because I have the start date and number of days so just want to find the end date.

 

[Caspius]

Hi,

There is an Excel function that will do this for you. You can create an Access VBA function using it like this:

Firstly create a new VBA module. Next you will need to add a reference to Excel. Go to the VBA editor (press Alt + F11) and select Tools > References from the menu. Scroll down the list and select Microsoft Excel.

Then enter the following code into the new module:

Public Function WorkingDay(StartDate As Date, NumberOfDays As Integer) As Date
    WorkingDay = Excel.WorksheetFunction.WorkDay(StartDate, NumberOfDays)
End Function

The function should then be available to your access query. Apologies as I haven't tested this.

How would I use this in a text box on a form or a query to get the answer?

 

[sparks80]

Have a look at the help file for the WorkDays function in excel - this is exactly what it does - adds a given number of days to a given date.

You can use functions written using VBA exactly the same as you use the built-in query functions - the only disadvantage is that it can be a bit slower to run.

So in your query you would use WorkingDay like any of the built-in functions:
DueDate: WorkingDay(#12/1/2012#, 12)

You can rename WorkingDay to whatever you like too - it might make more sense in your case to call the function MyDueDate or something like that. You would need to change the name in both the query and the VBA code.

This will ignore any public holidays, but could be modified to include them if necessary.

 

[Caspius]

I think you can write your oun function for this.
It shouldn't be too difficult.

What you should do is like this:
Add 1 day, if this is a non working day add another day - you can check against both weekend days and holidays table, and do the test again...
loop this untill you add the number of days you want.

Thanks for that, I'm getting closer. I found one here with similar idea: http://www.access-programmers.co.uk/forums/showthread.php?t=118886but I'm having trouble getting the result back in the form, its just keeps coming back with #Name? in the text box in the form.

Another here but looks overly complex when its easier to list holidays in a table: http://www.access-programmers.co.uk/forums/showthread.php?t=54510

 

[Caspius]

Have a look at the help file for the WorkDays function in excel - this is exactly what it does - adds a given number of days to a given date.

In your query you would use WorkingDay like the built-in functions:
DueDate: WorkingDay(#12/1/2012#, 12)

This will ignore any public holidays, but could be modified to include them if necessary.

Ahh yes thanks, holidays are the key or should I say 'pain in the arse' in all this. I'll it another go.

 

[smig]

a simple function to check for holidays:

function fnCheckHolidays(chkDate as Data) as boolean

If Dcount("[Holiday]", "HolidaysTable", "[HolidayDate] = #" & chkDate & "#") > 0 Then
  fnCheckHolidays = True
else
  fnCheckHolidays = False
end if
 
end function

 

[sparks80]

OK, the code below will take the holiday dates into account.

To call the function from your query use this:
DueDate: CalculateDueDate(Format([StartDate],"mm/dd/yyyy"), [NumberOfDays])

The format statement is needed, because VBA requires date in the American format - mm/dd/yyyy.

You will need to substitute your name for the holiday dates table and field, highlighted in red below.

Public Function CalculateDueDate(StartDate As Date, NumberOfDays As Integer) As Date
  Dim DateArray() As Date
  Dim lngRecordCount As Long
  Dim rst As New ADODB.Recordset
  Dim strSQL As String
 
  On Error GoTo Error_CalculateDueDate
 
  Dim I As Long
 
  ' get number of records in holiday table
  lngRecordCount = DCount("*", "[COLOR=Red]YourHolidayTableName[/COLOR]")
 
  ' set the array dimensions to hold all the dates in [COLOR=Red]YourHolidayTableName[/COLOR]
  ReDim DateArray(0 To lngRecordCount - 1) As Date
 
  strSQL = "SELECT * FROM [COLOR=Red]YourHolidayTableName[/COLOR]"
 
  rst.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly
 
  ' initialise record counter
  I = 0
 
  Do While Not rst.EOF
    ' Assign date to array
    ' Note need to convert the date to mm/dd/yyyy format as
    ' this is required by VBA
    DateArray(I) = Format(rst![COLOR=Red]YourHolidayDateFieldName[/COLOR], "mm/dd/yyyy")
    I = I + 1
    rst.MoveNext
  Loop
  rst.Close
 
  CalculateDueDate = Excel.WorksheetFunction.WorkDay(StartDate, NumberOfDays, DateArray())
 
Exit_CalculateDueDate:
  Set rst = Nothing
  Exit Function
Error_CalculateDueDate:
  ' An error occurred - return nothing
  Debug.Print Err.Description
  
  CalculateDueDate = 0
  GoTo Exit_CalculateDueDate
End Function

 

[RainLover]

The attached is something I was working on some time ago.

I never did get to tidy it up.

The functions work, you just need to adapt to your situation.

 

[hughesje]

Many thanks Sparks80, I have been searching for a simple way to do this and this was great, one thing for me (being a basic user!) was an 'Object' error so my slightly amended version is:

Public Function WorkingDay(StartDate As Date, NumberOfDays As Integer) As Date
 Dim xl As Object
     Set xl = CreateObject("Excel.Application")
 
    WorkingDay = xl.WorksheetFunction.WorkDay(StartDate, NumberOfDays)
Set xl = Nothing
End Function

Hi,

There is an Excel function that will do this for you. You can create an Access VBA function using it like this:

Firstly create a new VBA module. Next you will need to add a reference to Excel. Go to the VBA editor (press Alt + F11) and select Tools > References from the menu. Scroll down the list and select Microsoft Excel.

Then enter the following code into the new module:

Public Function WorkingDay(StartDate As Date, NumberOfDays As Integer) As Date
    WorkingDay = Excel.WorksheetFunction.WorkDay(StartDate, NumberOfDays)
End Function

The function should then be available to your access query. Apologies as I haven't tested this.