Text box in form needs to skip weekends

[Garyj]

I have a text box with this code.


=IIf([DateMailed]>[SLOON],"SERVICE LEVEL OBJECTIVE WAS NOT MEET FOR THIS JOB.")

I need to not include weekends in this calculation. How would I go about this. This code works fine if it wasn't for the weekend senorio. Any help would be appreciated.

Thanks

 

[GumbyD]

Garyj -

I think I can assist you, but I need more information. Which one of the date fields that you have referenced cannot be on the weekend? Since you are comparing two dates if a date falls on the weekend do you want to compare the following Monday's date or the preceeding Friday's date?

If you can answer those questions I can create an expression for you.

GumbyD

 

[Garyj]

Text box in form needs toskip weekends

=IIf([DateMailed]>[SLOON],"SERVICE LEVEL OBJECTIVE WAS NOT MEET FOR THIS JOB.")

text box 1 is [DateMailed]
text box 2 is [SLOON]
text box 3 is [Warning]

Now in text box 3 which has the above code I want it to calculate the difference between one and two and exclude the weekends. I hope this explains what I need.

Text box 3 should not show any weekend dates.

I appreciate your help
Thanks

 

[Lister]

The calculation of the number of working days between two given dates requires the exclusion of both holidays and 'weekend' days. Weekends are assumed to be trditional Saturday and Sunday, however Holidays need to be explicitly defined and removed from the working days between the dates. Further, some Holidays fall on WeekEnd days, and then need to NOT be removed from the count. One soloution is to create the holiday list (as a table), then do a simple loop from the starting date to the ending date, accumulating (as workdays) all of the dates which are not either weekend days or hollidays. This approach is shown below, with a 'sample' of the Holiday table.

HoliDate HoliName
1/1/00 New Year's Day
1/17/00 Martin Luther King Day
2/2/00 Groundhog Day
2/12/00 Lincon's Birthday
2/14/00 Valentine's Day
2/21/00 President's Day
2/22/00 Washington's Birthday
3/8/00 Ash Wednesday
3/17/00 St. Patrick's Day
4/1/00 April Fool's Day
4/20/00 Passover
4/21/00 Good Friday
And so on and so on...

DeltaDays is the name of this function.

First, Get the number of days between the dates
NumDays: DeltaDays([StDt], [EndDt])

Public Function DeltaDays(StartDate As Date, EndDate As Date) As Integer

'Get the number of workdays between the given dates

Dim dbs As Database
Dim rstHolidays As Recordset

Dim Idx As Long
Dim MyDate As Date
Dim NumDays As Long
Dim strCriteria As String
Dim NumSgn As String * 1

Set dbs = CurrentDb
Set rstHolidays = dbs.OpenRecordset("tblHolidays", dbOpenDynaset)

NumSgn = Chr(35)

MyDate = Format(StartDate, "Short Date")

For Idx = CLng(StartDate) To CLng(EndDate)
Select Case (WeekDay(MyDate))
Case Is = 1 'Sunday
'Do Nothing, it is NOT a Workday

Case Is = 7 'Saturday
'Do Nothing, it is NOT a Workday

Case Else 'Normal Workday
strCriteria = "[HoliDate] = " & NumSgn & MyDate & NumSgn
rstHolidays.FindFirst strCriteria
If (rstHolidays.NoMatch) Then
NumDays = NumDays + 1
Else
'Do Nothing, it is NOT a Workday
End If

End Select

MyDate = DateAdd("d", 1, MyDate)

Next Idx

DeltaDays = NumDays

End Function

Rember that this is a Public Function so create it in a module and call it in a query.
Works well for me, its slow some days and there could be better code to do it.

Hope this is some help

 

[Garyj]

Text box in form needs toskip weekends

I appreciate your help. I will try this out and let you know my results.

God is good not sometimes but all the time.

Thanks again

 

[Garyj]

text box in form need to skip weekends

How do I can this module with a query?

Where is this located?

First, Get the number of days between the dates
NumDays: DeltaDays([StDt], [EndDt])

Here is what I have, what did I do wrong?

Public Function HolidaySkip(StartDate As Date, EndDate As Date) As Integer
'Get the number of workdays between the given dates

Dim dbs As Database
Dim rstHolidays As Recordset

Dim Idx As Long
Dim MyDate As Date
Dim NumDays As Long
Dim strCriteria As String
Dim NumSgn As String * 1

Set dbs = CurrentDb
Set rstHolidays = dbs.OpenRecordset("tblHolidays", dbOpenDynaset)

NumSgn = Chr(35)

MyDate = Format(StartDate, "Short Date")

For Idx = CLng(StartDate) To CLng(EndDate)
Select Case (WeekDay(MyDate))
Case Is = 1 'Sunday
'Do Nothing, it is NOT a Workday

Case Is = 7 'Saturday
'Do Nothing, it is NOT a Workday

Case Else 'Normal Workday
strCriteria = "[HoliDate] = " & NumSgn & MyDate & NumSgn
rstHolidays.FindFirst strCriteria
If (rstHolidays.NoMatch) Then
NumDays = NumDays + 1
Else
'Do Nothing, it is NOT a Workday
End If

End Select

MyDate = DateAdd("d", 1, MyDate)

Next Idx

HolidaySkip = NumDays

Thank you

 

[GumbyD]

The only thing I see wrong with this is that you do not have an 'End Function' line on the code. I put the code in place here and it worked great. Is the system giving you an error message of any kind? In your query you just need to put in a new field like:

DayCount: HolidaySkip(#11/22/2003#,#11/30/2003#)

You can replace the dates with fields that have a date/time datatype also like this:

DayCount: HolidaySkip([HireDate],[TerminationDate])

or you can use a function that returns a date value like this:

DayCount: HolidaySkip([HireDate],date())

Good luck!

GumbyD