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

  6. After this macro how i get the result in excel sheet

  7. You don't generally have the foggiest idea how much an arranged meeting definitely thinks about you however it doesn't damage to appear for the date looking as if you have placed a lot of exertion into your appearance.

  8. How do I make money from playing games and earning
    These are งานออนไลน์ the three most popular forms of gambling, and are explained in a very concise and concise manner. 토토 사이트 The most common forms of gambling are:


Learn how to create Excel dashboards.
Yogesh Gupta's Excel Tips