Sunday, February 15, 2009

Custom Formating - Excel Date Format

Microsoft Excel stores dates as sequential numbers known as serial values. Excel stores times as decimal fractions because time is considered a portion of a day.

You can view them as you want based on how do you format the cells. There are predefined date formats available when you choose format cells option. However you can use custom format option to display dates if available formats does not serve your requirement.

You can choose following codes or combination of it to view date in excel as you want. I have used highlighted codes dd mm yyyy with "/" to show today's date as 16/02/2009 in above screen. You can use any of these code singly or jointly with any character in between.


  1. Do you know how to format it so that it drops the year? I have a bunch of dates of birth that I need to sort by mm/dd and can not use the year at all or else it uses the year to sort also.

  2. Hello Donna

    I have posted detailed response in my new post on sorting dates on Birthday

  3. sir I want to convert date (01/01/2000)as words like First January Two Thousand. Kindly advice me.

  4. @pecs,

    Give this UDF (user defined function) a try...

    Function DateToWords(ByVal DateIn As Variant) As String
    Dim Yrs As String
    Dim Hundreds As String
    Dim Decades As String
    Dim Tens As Variant
    Dim Ordinal As Variant
    Dim Cardinal As Variant
    Ordinal = Array("First", "Second", "Third", _
    "Fourth", "Fifth", "Sixth", _
    "Seventh", "Eighth", "Nineth", _
    "Tenth", "Eleventh", "Twelfth", _
    "Thirteenth", "Fourteenth", _
    "Fifteenth", "Sixteenth", _
    "Seventeenth", "Eighteenth", _
    "Nineteenth", "Twentieth", _
    "Twenty-first", "Twenty-second", _
    "Twenty-third", "Twenty-fourth", _
    "Twenty-fifth", "Twenty-sixth", _
    "Twenty-seventh", "Twenty-eighth", _
    "Twenty-nineth", "Thirtieth", _
    Cardinal = Array("", "One", "Two", "Three", "Four", _
    "Five", "Six", "Seven", "Eight", "Nine", _
    "Ten", "Eleven", "Twelve", "Thirteen", _
    "Fourteen", "Fifteen", "Sixteen", _
    "Seventeen", "Eighteen", "Nineteen")
    Tens = Array("Twenty", "Thirty", "Forty", "Fifty", _
    "Sixty", "Seventy", "Eighty", "Ninety")
    DateIn = CDate(DateIn)
    Yrs = CStr(Year(DateIn))
    Decades = Mid$(Yrs, 3)
    If CInt(Decades) < 20 Then
    Decades = Cardinal(CInt(Decades))
    Decades = Tens(CInt(Left$(Decades, 1)) - 2) & "-" & _
    Cardinal(CInt(Right$(Decades, 1)))
    End If
    Hundreds = Mid$(Yrs, 2, 1)
    If CInt(Hundreds) Then
    Hundreds = Cardinal(CInt(Hundreds)) & " Hundred "
    Hundreds = ""
    End If
    DateToWords = Ordinal(Day(DateIn) - 1) & _
    Format$(DateIn, " mmmm ") & _
    Cardinal(CInt(Left$(Yrs, 1))) & _
    " Thousand " & Hundreds & Decades
    End Function

    1. sir,
      I need Date into word for Date of Birth format Example 13/06/1996 into thirteen june nineteen ninety six

    2. @RockSathi,

      Did the code I posted not work for you? If not, what exactly was the problem?

  5. date to words in hindi function