DateAdd to add back weekends and holidays

[vbagwell1967]

I have a database created, that logs RFQs (Request for Quotes). The purpose of the log is to be able to identify Quotes that are past due, on time, coming due, and the total time the quote was a WIP. We receive emails from clients requesting these quotes - the date the email was received is a datestamp field called "RecdDate". We have a SQL Server backend and the date was set to European format "yyyy-mm-dd" which I have no control over. Below is the SQL code that I have so far:



SELECT dbo_tblQuoteTransactions.ProjName, dbo_tblQuoteTransactions.RecdDate, DateValue([RecdDate]) AS RecdDateUS, dbo_tblTURNAROUND.TURN_DAYS
FROM dbo_tblQuoteTransactions INNER JOIN dbo_tblTURNAROUND ON dbo_tblQuoteTransactions.TURN_ID = dbo_tblTURNAROUND.TURN_ID;

When the associate logs the RFQ, they have a dropdown box that they can select the turnaround time (dbo_tblTURNAROUND.TURN_DAYS) that needs to be applied to the quote, which determines the DueDate. So in layman's terms, the formula is:

RecdDate + dbo_tblTURNAROUND.TURN_DAYS + Weekends + Holidays, i.e., we do not want the weekends or holidays to be a part of the turnaround time - only weekdays.

Problems:

1. I understood that the "w" in the formula: DueDate: DateAdd("w",[TURN_DAYS],[RecdDateUS]), is suppose to add only weekdays - this is not working.
2. I have looked at a bunch of code, and I cannot get the weekend and holidays added back to the DueDate - most code I saw is working on a single date, but I am working with a range.

Here is my output to the query:

This is the only thing that is keeping me from completing this project. Any help would be much appreciated!

[jwhite]

Here is an example:
https://social.msdn.microsoft.com/Fo...3?forum=isvvba

If you don't want to create a holiday table and bump those days as well, then remove the check for it in the If statement.

[ssanfu]

FWIW, in Help:

"When you use the "w" interval (which includes all the days of the week, Sunday through Saturday) to add days to a date, the DateAdd function adds the total number of days that you specified to the date, instead of adding just the number of workdays (Monday through Friday) to the date, as you might expect."

[vbagwell1967]

Hi,

I am still not getting this. I found the following code and tried to use it, but I am not sure that I completely understand it. Please, can someone walk me through this?

Function addWorkDays(DaysBetween As Long, DueDate As Date) As Date , where "DaysBetween" is an interval selected from a dropdown box, and "DueDate" is a calculated field using "RecdDate" and "DaysBetween"


Dim RecdDate As Date , where "RecdDate" is the date the RFQ was received
Dim I As Long, tmpDate As Date
tmpDate = RecdDate , is this correct - the "TmpDate" is set to the "RecdDate"?
I = 1
Do While I <= DaysBetween , I understand this is the loop condition to loop until variable "I" is less than or equal to DaysBetween"
If Weekday(tmpDate) <> 1 And Weekday(tmpDate) <> 7 And _ , I understand that this is checking the "RecdDate" to determine if it is a Saturday or Sunday (because "tmpDate" = "RecdDate"
DCount("*", "tbl_Holidays", "HolidayDate = " & CDbl(tmpDate)) = 0 Then I = I + 1 , and is looking at the "Holidays" table and performing an aggregate count of "HolidayDate" that matchup in the "Holidays" table. What is "CDbl(TmpDate))?
tmpDate = DateAdd("d", 1, tmpDate)
Loop

addWorkDays = tmpDate ,What is this doing?



End Function



Please see my output below:

[ssanfu]

The UDF (user defined function) addWorkDays returns a date based on the parameter "DaysBetween" interval and the parameter "DueDate" date.

Function addWorkDays(DaysBetween As Long, DueDate As Date) As Date 
'  where "DaysBetween" is an interval  selected from a dropdown box, and "DueDate" is a calculated field using  "RecdDate" and "DaysBetween"

'********************
'Code Courtesy of
'  Paul Eugin
'********************
    Dim RecdDate As Date 'where "RecdDate" is the date the RFQ was received
       'in this function, the variable "RecdDate" is not used, so it can be deleted

    Dim tmpDate As Date
    Dim I As Long
                            
    tmpDate = DueDate    'is this correct - the "TmpDate" is set to the "RecdDate"?
       'No, tmpDate should be set to the parameter "DueDate"
    I = 1
    Do While I <= DaysBetween
     ' I don't like using the continuation line, so I changed the IF() function to the block syntax.
        If Weekday(tmpDate) <> 1 And Weekday(tmpDate) <> 7  And DCount("*", "tbl_Holidays", "HolidayDate = " & CDbl(tmpDate)) = 0  Then 
           I = I + 1
        End If
'I understand that this is checking the "RecdDate" to determine if it is  a Saturday or Sunday (because "tmpDate" = "RecdDate" 
' and  is looking at the "Holidays" table and performing an aggregate  count  of  "HolidayDate" that matchup in the  "Holidays" table.  
What is  "CDbl(TmpDate))?
'No, this is checking if "tmpDate" is a weekend day. If "tmpDate" is not Sat or Sun AND "tmpDate" in not in tbl_Holidays, the use the DateAdd function to a one day to tmpDate.
        tmpDate = DateAdd("d", 1, tmpDate)
    Loop
    
    addWorkDays = tmpDate  'What is this doing?
      'This is how you return the value of calculations in a function. 

End Function

What is "CDbl(TmpDate))?
The function CDbl() converts the parameter to a number- double type. Even though you see a "Date" in a table, it is actually stored as a double number type. Access has code to show you the number as a Date even though it is a number. This is why you can add 1 to a date and get a date.

If you had code that looked like

Dim tmpDate as Date

tmpDate = Date()
tmpdate = tmpdate + 1

executing the code would return tomorrow's date.

In the immediate window, try
? date()+1



For clarity, here is the code

Function addWorkDays(DaysBetween As Long, DueDate As Date) As Date
'********************
'Code Courtesy of
'  Paul Eugin
'********************

    Dim tmpDate As Date
    Dim I As Long

    tmpDate = DueDate
    I = 1
    Do While I <= DaysBetween
        If Weekday(tmpDate) <> 1 And Weekday(tmpDate) <> 7 And DCount("*", "tbl_Holidays", "HolidayDate = " & CDbl(tmpDate)) = 0 Then
            I = I + 1
        End If
        tmpDate = DateAdd("d", 1, tmpDate)
    Loop

    addWorkDays = tmpDate    'return the date

End Function

that gives these results

Is this what you want???