Table of Contents
DatePart() Function (VB work-a-like)
The following is a DatePart function for returning the numerical value of part of the supplied datetime.
It is inspired by the VB function of the same name but is tweaked slightly from the original in that only a subset of intervals are supported as follows;
yyyy - Year mm - Month dd - Day h - Hour m - Minute s - Second
It is easy enough to derive these values yourself by slicing the string and returning the Val() but DatePart draws all the various sections into one useful and compact function thus eliminating errors from extracting the wrong sub string etc or by having to remember where the relevant section appears in the datetime string.
There is no error checking so if you supply nonsense you will probably crash it or get rubbish back. It only works reliably with datetimes formatted as below.
Notes:
DateTimes must conform to the format DD-MM-YYYY HH:MM:SS e.g. 25-12-2005 19:25:45 International sort format for dates (YYYY-MM-DD) is not supported (yet)
Syntax:
=DatePart(Interval,DateStr)
Examples:
Print DatePart(“mm”,“01-01-1970 00:00:00”) ' returns 1 Hour=DatePart(“h”,Now()) ' get the hour of the current time LastYear=DatePart(“yyyy”,Now())-1 ' previous year from today
Dependencies:
None
Function DatePart(Interval As String,dt As String) As Integer Select Case LCase$(Interval) Case "s"' Seconds DatePart=Val(Right$(dt,2)) Case "m"' Minutes DatePart=Val(Mid$(dt,15,2)) Case "h"' Hours DatePart=Val(Mid$(dt,12,2)) Case "dd" 'Days DatePart=Val(Left$(dt,2)) Case "mm"' calendar Month DatePart=Val(Mid$(dt,4,2)) Case "yyyy"' Year DatePart=Val(Mid$(dt,7,4)) Case Else DatePart=0 End Select End Function
See Also:
DateAdd()
DateDiff()
Now()