Solved

# Lookup price from the previous day but exclude weekends

Posted on 2014-11-23
108 Views
Hi Experts

Everyday, except weekends, product pricing for buyers gets entered into our system.  I am wanting to do a lookup of the previous day pricing that was entered for a product and buyer.  It will be based on buyer, date, product and price.

Cheers
Ajae
0
Question by:ajaeclarke
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 51

Accepted Solution

Rgonzo1971 earned 500 total points
ID: 40461514
Hi,

if the last input is on friday, you only have to look for the last one but if you want to calculate the Business Day -1 you could use

a workaround from MS support itself since the function DateAdd with the argument "w" weekday doesn't work

http://support.microsoft.com/kb/115489/en-us

and test it with

Regards
0

LVL 50

Expert Comment

ID: 40461648
The Microsoft function will fail in a non-English environment.

You can use this (basic) expression and the function below:

=DLookup("[YourPriceField]", "[tblYourTable]", "[YourDateField] = DateAddWorkdays(-1, Date())")
``````Public Function DateAddWorkdays( _
ByVal lngNumber As Long, _
ByVal datDate As Date, _
Optional ByVal booWorkOnHolidays As Boolean) _
As Date

'   Adds lngNumber of workdays to datDate.
'   2014-10-03. Cactus Data ApS, CPH

' Calendar days per week.
Const clngWeekdayCount  As Long = 7
' Workdays per week.
Const clngWeekWorkdays  As Long = 5
' Average count of holidays per week maximum.
Const clngWeekHolidays  As Long = 1
' Maximum valid date value.
Const cdatDateRangeMax  As Date = #12/31/9999#
' Minimum valid date value.
Const cdatDateRangeMin  As Date = #1/1/100#

Dim aHolidays() As Date

Dim lngDays     As Long
Dim lngDiff     As Long
Dim lngDiffMax  As Long
Dim lngSign     As Long
Dim datDate1    As Date
Dim datDate2    As Date
Dim datLimit    As Date
Dim lngHoliday  As Long

lngSign = Sgn(lngNumber)
datDate2 = datDate

If lngSign <> 0 Then
If booWorkOnHolidays = True Then
' Holidays are workdays.
Else
' Retrieve array with holidays between datDate and datDate + lngDiffMax.
' Calculate the maximum calendar days per workweek.
lngDiffMax = lngNumber * clngWeekdayCount / (clngWeekWorkdays - clngWeekHolidays)
' Add one week to cover cases where a week contains multiple holidays.
lngDiffMax = lngDiffMax + clngWeekdayCount
aHolidays = GetHolidays(datDate, datDate1)
End If
Do Until lngDays = lngNumber
If lngSign = 1 Then
datLimit = cdatDateRangeMax
Else
datLimit = cdatDateRangeMin
End If
If DateDiff("d", DateAdd("d", lngDiff, datDate), datLimit) = 0 Then
' Limit of date range has been reached.
Exit Do
End If

lngDiff = lngDiff + lngSign
Select Case Weekday(datDate2)
Case vbSaturday, vbSunday
' Skip weekend.
Case Else
' Check for holidays to skip.
' Ignore error when using LBound and UBound on an unassigned array.
On Error Resume Next
For lngHoliday = LBound(aHolidays) To UBound(aHolidays)
If Err.Number > 0 Then
' No holidays between datDate and datDate1.
ElseIf DateDiff("d", datDate2, aHolidays(lngHoliday)) = 0 Then
' This datDate2 hits a holiday.
' Subtract one day before adding one after the loop.
lngDays = lngDays - lngSign
Exit For
End If
Next
On Error GoTo 0
lngDays = lngDays + lngSign
End Select
Loop
End If

End Function
``````
If you have a table of Holidays, it can use this as well.

/gustav
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
###### Suggested Courses
Course of the Month5 days, 2 hours left to enroll