数據處理離不開日期處理,以前經常是每次用到又重寫一次函數,挺浪費時間的。現整理了一些關于日期的實用函數,大家參考。附件中也有代碼,供下載用。
Public Function IsLeapYear(YearNumber As Integer) As Boolean
Dim dteLeapDay As Date
' if particular year is not leap year,
' dteLeapDay will become Mar-01
dteLeapDay = DateSerial(YearNumber, 2, 29)
IsLeapYear = (Month(dteLeapDay) = 2)
End Function
' Based on vdate, the function will return the
' first day of the month
Public Function FirstDayOfMonth(vdate As Date) As Date
Dim intCurrDate As Integer ' current date
intCurrDate = DatePart("d", vdate)
' Deduct intCurrDate - 1 days
FirstDayOfMonth = DateAdd("d", -intCurrDate + 1, vdate)
End Function
' Calculate first day of the month (method two)
' This method is quite straightforward
Public Function FirstOfMonth(vdate As Date) As Date
FirstOfMonth = DateSerial(DatePart("yyyy", vdate), DatePart("m", vdate), 1)
End Function
' Based on vdate, the function will
' return the last day of the month
Public Function LastDayOfMonth(vdate As Date) As Date
' Get the first day of the next month, and then deduct 1 day
LastDayOfMonth = DateSerial(DatePart("yyyy", vdate), DatePart("m", vdate) + 1, 1) - 1
End Function
' The function will return the first day of the year based on vdate
Public Function FirstDayOfYear(vdate As Date) As Date
FirstDayOfYear = DateSerial(Year(Date), 1, 1)
End Function
' The function will return the first day of the week, based on vdate
Public Function FirstDayOfWeek(vdate As Date) As Date
Dim intWeekday As Integer
intWeekday = Weekday(vdate, vbMonday)
FirstDayOfWeek = DateAdd("d", -intWeekday + 1, vdate)
End Function
' The function will return the last day of the week, based on vdate
' Monday is the first day (assumed)
Function LastDayOfWeek(vdate As Date) As Date
Dim intWeekday As Integer
intWeekday = Weekday(vdate, vbMonday)
LastDayOfWeek = DateAdd("d", -intWeekday + 1, vdate) + 6
End Function
' The function will return number of week days between two dates
Function NumberOfWeekdays(BeginDate As Date, EndDate As Date, IncludingFirstDay As Boolean) As Integer
Dim intCounter As Integer
Dim intMovingDate As Date
intCounter = 0
If IncludingFirstDay = True Then
intMovingDate = BeginDate
Else
intMovingDate = BeginDate + 1
End If
Do While intMovingDate <= EndDate ' End date inclusive
If Weekday(intMovingDate) <> vbSunday And Weekday(intMovingDate) <> vbSaturday Then
intCounter = intCounter + 1
End If
intMovingDate = intMovingDate + 1
Loop
Access软件网交流QQ群(群号:198465573)