Date Stuff – Useful Examples

This post is the third in a 3-part series on Date functions in Access. If you have not read the first two posts, it might be worth while to do so.

1. Date Stuff – Functions
2. Date Stuff – Using the functions
3. Date Stuff – Useful Examples (this post)

To see working examples, please see the following sample: DateStuff.mdb on my website: www.RogersAccessLibrary.com.

Useful Examples

As with most things in Access, there are multiple ways to do most each of the things below, so I'll list at least a couple of methods.  There may be more.

First Day of the Current Month

• DateSerial(DatePart("yyyy", Date()), DatePart("m", Date()),1)
• DateAdd("d",1-DatePart("d",Date()),Date())
• CDate(CStr(DatePart("m",Date())) & "/1")

Used in a UDF (User Defined Function). Any of the above formulas can be substituted:

Function FirstofMonth(vdate As Date)
FirstofMonth = DateAdd("d",1-DatePart("d",Date()),Date())
End Function

Last Day of the Current Month

• DateSerial(DatePart("yyyy",Date()),DatePart("m",Date())+1,1)-1
• DateAdd("m",1,CDate(CStr(DatePart("m",Date())) & "/1"))-1

Used in a UDF (User Defined Function). Any of the above formulas can be substituted:

Function LastofMonth(vdate As Date)
LastofMonth =DateAdd("m",1,CDate(CStr(DatePart("m",Date())) & "/1"))-1
End Function

First Day of the Previous Month

• DateSerial(DatePart("yyyy",Date()),DatePart("m",Date())-1,1)
• DateAdd("m",-1,CDate(CStr(DatePart("m",Date())) & "/1"))

Used in a UDF (User Defined Function). Any of the above formulas can be substituted:

Function FirstofPrevMonth(vdate As Date)
FirstofPrevMonth=DateAdd("d",1-DatePart("d",Date()),Date()) – 1
End Function

Last Day of the Previous Month

• DateSerial(DatePart("yyyy", Date()), DatePart("m",Date()),1)-1
• DateAdd("d",1-DatePart("d",Date()),Date()) - 1
• CDate(CStr(DatePart("m",Date())) & "/1") – 1

Used in a UDF (User Defined Function). Any of the above formulas can be substituted:

Function LastofPrevMonth(vdate As Date)
LastofPrevMonth=DateAdd("d",1-DatePart("d",Date()),Date())–1
End Function

First Day of the Next Month

• DateSerial(DatePart("yyyy",Date()),DatePart("m",Date())+1,1)
• DateAdd("m",1,CDate(CStr(DatePart("m",Date())) & "/1"))

Used in a UDF (User Defined Function). Any of the above formulas can be substituted:

Function FirstofNextMonth(vdate As Date)
FirstofNextMonth = DateAdd("m",1,CDate(CStr(DatePart("m",Date()))& "/1"))
End Function

Last Day of the Next Month

• DateSerial(DatePart("yyyy",Date()),DatePart("m",Date())+2,1)-1
• DateAdd("m",1,CDate(CStr(DatePart("m",Date())+1) & "/1")-1)

Used in a UDF (User Defined Function). Any of the above formulas can be substituted:

Function LastofNextMonth(vdate As Date)
LastofNextMonth = DateAdd("m",1,CDate(CStr(DatePart("m",Date())+1) & "/1")-1)
End Function

First Day of This Week

• DateDiff("d",Weekday(Date()-1,1),Date())
• Date()-Weekday(Date())+1

Used in a UDF (User Defined Function). Any of the above formulas can be substituted:

Function FirstofWeek(vdate As Date)
FirstofWeek= DateDiff("d",Weekday(Date()-1,1),Date()) 
End Function

Last Day of This Week

• DateDiff("d",Weekday(Date()-1,1),Date())+6
• Date()+(7-Weekday(Date()))

Used in a UDF (User Defined Function). Any of the above formulas can be substituted:

Function LastOfWeek(vdate As Date)
LastOfWeek= DateDiff("d",Weekday(Date()-1,1),Date())+6
End Function

First Work Day of This Week

• DateDiff("d",Weekday(Date()-1,2),Date())
• Date()-Weekday(Date())+2

Used in a UDF (User Defined Function). Any of the above formulas can be substituted:

Function FirstWorkdayOfWeek(vdate As Date)
FirstWorkdayOfWeek= DateDiff("d",Weekday(Date()-1,2),Date())
End Function

Last Work Day of This Week

• DateDiff("d",Weekday(Date()-1,2),Date())+4
• Date()+(6-Weekday(Date()))

Used in a UDF (User Defined Function). Any of the above formulas can be substituted:

Function LastWorkdayOfWeek(vdate As Date)
LastWorkdayOfWeek= DateDiff("d",Weekday(Date()-1,2),Date())+4 
End Function

Number of Week Days

This function will calculate the number of week days between any two dates.

Function NumberOfWeekdays(begindate As Date,
                 EndDate As Date,
                 bolInclusive As Boolean) As Integer

Dim intCounter As Integer
Dim intMovingDate As Date
intCounter = 0
If bolInclusive Then
    intMovingDate = begindate
    EndDate = EndDate + 1
Else
    intMovingDate = begindate + 1
End If
Do While intMovingDate < EndDate
    If WeekDay(intMovingDate) <> 1 And WeekDay(intMovingDate) <> 7 Then
        intCounter = intCounter + 1
    End If
        intMovingDate = intMovingDate + 1
    Loop
NumberOfWeekdays = intCounter
End Function

Calling the function. (The third argument determines whether you want to include the begin and end dates in the weekday count.)

Sub testweekdays()
'uses the FirstOfMonth function above. Literal dates like #1/1/2012# can also be used.
Dim intdays As Integer
    intdays = NumberOfWeekdays(FirstofMonth(Date), Date, True)
    MsgBox intdays
End Sub