Useful date functions

By John — in background, Calendar, dates, user defined functions · 3 min read
Useful date functions

Here are a selection of date functions I regularly use to either transform text into date values , or vice versa.  Paste these into your project if you're presented with a sheet where users have manually typed such as '31st January 2012' or similar.

 A quick rundown of what each does.. 

DateTextToValue   ( Date String , String  )

Does what it says on the tin, combining all other functions, will take dates in the format '31st January 2012' and turn them into an excel date format , in this case 40939.

This can then be read for measures/dimensions in pivot charts.

 DateClearNth( Ordinal Number as String ) - returns Integer

This will take any ordinal number string i.e.  '21st' or '3rd' and return an integer value.

DateMonTextNum ( MonthName as String ) - returns Integer ( limited to 1-12 )

This will take any month name as text i.e. "Jan" or "January" and return the corresponding month number.

OrdinalDate( Date , Output Type Integer )  - Returns String based on Type

 Below is an example of the output :

  • 0 - returns just the Ordinal Day portion only
  • 1 - returns just the Ordinal Day + Month
  • 3  - returns just the Ordinal Day + Month + Year

Ordinal-date-function

 

 

 

 

Finally in this set, I've included a function to split a string by delimiter, and return the numbered portion of the string. 

SplitDel ( String , Delimiter, Index )  - returns string at Index 


split-by-delimited-ordered-example

In the example  above, the text string has spaces between components , so "2nd Feb 2017" can be broken down into :

SplitDel(B5," ",0 ) = "2nd"
SplitDel(B5," ",1 ) = "Feb"
SplitDel(B5," ",2 ) = "2017"

 Full code : 


Function DateTextToValue(InputSt As String, Optional DelTxt As String = " ") As Date
'Default to Space as delimiter, override where specified
DateTextToValue = DateValue(DateClearNth(SPLITDel(InputSt, DelTxt, 0)) & "/" & _
DateMonTextNum(SPLITDel(InputSt, DelTxt, 1)) & "/" & _
SPLITDel(InputSt, DelTxt, 2))

End Function

Function DateClearNth(InputSt As String) As Integer

InputSt = Replace(InputSt, "th", "")
InputSt = Replace(InputSt, "st", "")
InputSt = Replace(InputSt, "rd", "")
InputSt = Replace(InputSt, "nd", "")

'MsgBox InputSt
DateClearNth = Val(Int(InputSt))

End Function

Function DateMonTextNum(InputSt As String) As Integer

InputSt = Left(Trim(UCase(InputSt)), 3)
DateMonTextNum = -1

For AA = 1 To 12
If InputSt = Left(UCase(Format(DateValue("01/" & AA & "/2001"), "MMMM")), 3) Then DateMonTextNum = AA
Next AA

End Function




Function OrdinalDate(MYDATE As Date, DMYT As Integer)
    Dim dDate As Integer
    Dim dText As String
    Dim mDate As Integer
    Dim mmmText As String
    Dim yYear As Integer

    dDate = Day(MYDATE)
    yYear = Year(MYDATE)

    Select Case dDate
        Case 1: dText = "st"
        Case 2: dText = "nd"
        Case 3: dText = "rd"
        Case 21: dText = "st"
        Case 22: dText = "nd"
        Case 23: dText = "rd"
        Case 31: dText = "st"
        Case Else: dText = "th"
    End Select

    mmmText = " " & Format(MYDATE, "MMMM")
    
    Select Case DMYT
    Case 0:    OrdinalDate = dDate & dText
    Case 1:    OrdinalDate = dDate & dText & mmmText
    Case 2:    OrdinalDate = dDate & dText & mmmText & " " & yYear
    End Select
    
End Function


Function DateMonTextNum(InputSt As String) As Integer

InputSt = Left(Trim(UCase(InputSt)), 3)
DateMonTextNum = -1

For AA = 1 To 12
If InputSt = Left(UCase(Format(DateValue("01/" & AA & "/2001"), "MMMM")), 3) Then DateMonTextNum = AA
Next AA

End Function


Function SPLITDel(TXT As String, Delim As String, Indx As Integer) As String

Dim TrStr() As String

TrStr = Split(TXT, Delim, -1)

On Error Resume Next
SPLITDel = TrStr(Indx)

End Function
Vb
Tagged as background, Calendar, dates and user defined functions

John —

Started out in media/graphic design then moved to Financial Services / MI data / Business intelligence. ~15 years experience of Excel / VBA , and more recently Qliksense , SQL

  • [CONTACT]
  • [LOCATION]
    London, UK