Three Handy Formatting Functions

Introduction

Here are three functions that I use regularly in my VBScript code. You are welcome to use them in yours!

FormatAmericanDate

This function simply takes a date in the English format dd/mm/yyyy and converts it to the American format mm/dd/yyyy.

If your computer and/or server has its regional settings set to English (United Kingdom), then you will frequently need to make conversions like this if you are compiling SQL queries in your code, since dates in SQL must be given in the American format.

function FormatAmericanDate(EnglishDate)
  'Converts an English date dd/mm/yyyy into an American date mm/dd/yyyy
  'Written by Tim Surtell and downloaded from www.surtell.com
  '(C) 2004 Tim Surtell
  'This code may be freely distributed providing that the credit above is retained

  if isDate(EnglishDate) then
    EnglishDate = CDate(EnglishDate)
    FormatAmericanDate = Mid(EnglishDate, 4, 3) & Left(EnglishDate, 3) & Right(EnglishDate, 4)
  else
    FormatAmericanDate = Null
  end if
end function

The ASP code below shows how this function could be used when compiling an SQL query. Note that the function will also work in reverse, formatting an American date into an English one!

dim SQLStatement
dim QueryDate

QueryDate = "25/1/2003"
response.write "<p>Query Date: " & QueryDate & "</p>"

SQLStatement = "SELECT * FROM People WHERE " & "DateOfBirth < #" & FormatAmericanDate(QueryDate) & "# ORDER BY LastName"
response.write "<p>SQL Statement: " & SQLStatement & "</p>"

FormatSpokenDate

This function takes a date in the English format dd/mm/yyyy and converts it to a string in the "spoken" form dayname ddth monthname yyyy. I find that in some situations this is more readable and personal than simply displaying a short date, and I am surprised it is never included as an formatting option in software such Windows and Microsoft Word.

You could easily modify the function to display shortened day and month names using Left if you wish, e.g. Thu 25th Dec 2003.

function FormatSpokenDate(EnglishDate)
  'Converts an English date dd/mm/yyyy into a 'spoken' date dayname ddth monthname yyyy
  'Written by Tim Surtell and downloaded from www.surtell.com
  '(C) 2004 Tim Surtell
  'This code may be freely distributed providing that the credit above is retained.

  if isDate(EnglishDate) then
    EnglishDate = CDate(EnglishDate)
    FormatSpokenDate = WeekdayName(Weekday(EnglishDate)) & " " & Day(EnglishDate)
    select case Day(EnglishDate)
      case 1, 21, 31
        FormatSpokenDate = FormatSpokenDate & "st"
      case 2, 22
        FormatSpokenDate = FormatSpokenDate & "nd"
      case 3, 23
        FormatSpokenDate = FormatSpokenDate & "rd"
      case else
        FormatSpokenDate = FormatSpokenDate & "th"
    end select
    FormatSpokenDate = FormatSpokenDate & " " & MonthName(Month(EnglishDate)) & " " & Year(EnglishDate)
  else
    FormatSpokenDate = Null
  end if
end function

The ASP code below shows how this function is used:

dim ShortDate

ShortDate = "25/12/2003"

response.write "<p>Short Date: " & ShortDate & "</p>"
response.write "<p>Spoken Date: " & FormatSpokenDate(ShortDate) & "</p>"

TitleCase

Here is another function which should really be built into VBScript. I use this function primarily to format input, since many people don’t bother to use capital letters properly when they are filling in forms online. You frequently get data input all in lower case (e.g. ‘tim surtell’) or all in upper case (e.g. ‘TIM SURTELL’) which poses a problem if you need to use the data later on in your software. For example, it would look strange (and unprofessional) if you sent an email beginning with ‘Dear TIM’.

On the subject of data input, you will find lots of people enter email addresses in uppercase when they don’t need to, so it is a good idea to use LCase on these before doing anything with them.

function TitleCase(TitleString)
  'Converts a string into Title Case
  'Written by Tim Surtell and downloaded from www.surtell.com
  '(C) 2004 Tim Surtell
  'This code may be freely distributed providing that the credit above is retained

  dim TempString, NextCap, CurrentChar

  NextCap = True

  for CurrentChar = 1 to Len(TitleString)
    if NextCap = True then
      TempString = TempString & UCase(Mid(TitleString, CurrentChar, 1))
      NextCap = False
    else
      TempString = TempString & Mid(TitleString, CurrentChar, 1)
    end if
    if InStr(" ({[""""", Mid(TitleString, CurrentChar, 1)) > 0 then
      NextCap = True
    end if
  next

  TitleCase = TempString

end function

The TitleCase function will work correctly most of the time, except in the case of names like O’Brian or McDonald, where it won’t be able to capitalise the B or the second M. However, the function only ever alters the first letter of a word, so if the visitor entered o’Brian this would be correctly converted to O’Brian. If you want to make sure only the first letter of each word is capitalised, then use LCase before calling TitleCase, as shown in the example usage below.

Note that TitleCase will also capitalise letters after opening brackets and double quotes — normally what you want.

dim InputString

InputString = "(sittin' on) the DOCK of the Bay"
response.write "<p>InputString: " & InputString & "</p>"

response.write "<p>Converted to Title Case: " & TitleCase(InputString) & "</p>"
response.write "<p>Converted to lower case, then Title Case: " & TitleCase(LCase(InputString)) & "</p>"