Date Functions
Functions available here include:
fnGetDate() :- returns a specific date related to the passed date via a textual
spec.
fnGetDOW() :- Returns the name of the day of the week for a date.
=========================================================================================================
Public Function
fnGetDate(dteInput As Date, strType As String) As Date
Dim dteD As Date '-- working date
'---- Variety of
date-returning functions.
Select Case strType
Case Is = "End of last year"
dteD = CDate("31/Dec/" & Year(dteInput) - 1)
Case Is = "End of last month"
dteD = CDate("01/" & Month(dteInput) & "/" &
Year(dteInput))
dteD = DateAdd("d", -1, dteD)
Case Is = "End of next month"
dteD = DateAdd("m", 2, dteInput)
dteD = CDate("01/" & Month(dteD) & "/" &
Year(dteD))
dteD = DateAdd("d", -1, dteD)
Case Is = "End of month"
dteD = CDate("01/" & Month(dteInput) & "/" &
Year(dteInput))
dteD = DateAdd("m", 1, dteD)
dteD = DateAdd("d", -1, dteD)
Case Is = "Start of month"
dteD = CDate("01/" & Month(dteInput) & "/" &
Year(dteInput))
Case Is = "Start of Year"
dteD = CDate("01/Jan/" & Year(dteInput))
Case Is = "Start of previous quarter"
dteD = fnGetDate(dteInput, "Start of month")
dteD = DateAdd("m", -3, dteD)
Case Is = "End of previous quarter"
dteD = dteInput
'---- Find the previous end of month where the month No is 3, 6, 9 or 12
Do
dteD = fnGetDate(dteD, "end of last
month")
Select Case Month(dteD)
Case Is = 3, 6, 9, 12
Exit Do
End Select
Loop
Case Is = "End of next quarter"
dteD = dteInput
'---- Find the next end of month where the month No is 3, 6, 9 or 12
Do
dteD = fnGetDate(dteD, "end of next
month")
Select Case Month(dteD)
Case Is = 3, 6, 9, 12
Exit Do
End Select
Loop
End Select
fnGetDate = dteD
End Function
=========================================================================================================
Public Function
fnGetDOW(dteInput As Date) As String
fnGetDOW = Weekday(dteInput)
Select Case fnGetDOW
Case Is = 1
fnGetDOW = "Sunday"
Case Is = 2
fnGetDOW = "Monday"
Case Is = 3
fnGetDOW = "Tuesday"
Case Is = 4
fnGetDOW = "Wednesday"
Case Is = 5
fnGetDOW = "Thursday"
Case Is = 6
fnGetDOW = "Friday"
Case Is = 7
fnGetDOW = "Saturday"
End Select
End Function
=========================================================================================================
Back to ...
Code Library Menu