Thread: Prior day Now()-1 excluding weekend (2000)

1. Prior day Now()-1 excluding weekend (2000)

Anyone ever wanted to fill a date field with the prior weekday (excluding weekend days)?

Thanks,
Howard

2. Re: Prior day Now()-1 excluding weekend (2000)

Yes - it basically involves calculating the weekday to see if it is a weekend, and if it is, subtracting either 1 or 2 more depending on whether the Now()-1 is Saturday or Sunday. Use the expression builder or help to see how Weekday() works.

3. Re: Prior day Now()-1 excluding weekend (2000)

It's tempting to reply "Yes" and leave it at that <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>.

Put this function in a standard module:

' Returns the last workday before aDate

Dim d As Date
PreviousWorkday = Null
Exit Function
End If
Do While WeekDay(d) = vbSunday Or WeekDay(d) = vbSaturday
d = d - 1
Loop
PreviousWorkday = d
End Function

To get the prior workday to today, use PreviousWorkday(Date())
In an expression (e.g. the control source of a text box), this would become
=PreviousWorkday(Date())

Note: this function doesn't take holidays into account.

4. Re: Prior day Now()-1 excluding weekend (2000)

Thanks for the code sample Hans - I started thinking about the holidays too. I came up with

Function PriorWorkDay()

Dim db As Database
Dim holidays As Recordset
Dim test As Date
Dim increment As Integer

Set db = CurrentDb
Set holidays = db.OpenRecordset("tblholidays")
holidays.Index = "PrimaryKey"

increment = 1
Do While increment
test = Date - increment
If Weekday(test) <> 1 And Weekday(test) <> 7 Then 'if not a weekend
holidays.Seek "=", test
If holidays.NoMatch Then 'not a holiday
PriorWorkDay = test
GoTo PriorWorkDay_Exit
End If
End If
increment = increment + 1
Loop

PriorWorkDay_Exit:
holidays.Close
db.Close
Exit Function

PriorWorkDay_Error:
MsgBox Err & " " & Error
Resume PriorWorkDay_Exit

End Function

My tblHolidays table has two fields, Date and Description. I have an index called "PrimaryKey" on the Date field. This seems to also work, and bypasses the holidays.

Thanks,

Howard

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•