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.
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.
ReplyDeleteHello Donna
ReplyDeleteI have posted detailed response in my new post on sorting dates on Birthday
sir I want to convert date (01/01/2000)as words like First January Two Thousand. Kindly advice me.
ReplyDelete@pecs,
ReplyDeleteGive 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", _
"Thirty-first")
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))
Else
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 "
Else
Hundreds = ""
End If
DateToWords = Ordinal(Day(DateIn) - 1) & _
Format$(DateIn, " mmmm ") & _
Cardinal(CInt(Left$(Yrs, 1))) & _
" Thousand " & Hundreds & Decades
End Function
sir,
DeleteI need Date into word for Date of Birth format Example 13/06/1996 into thirteen june nineteen ninety six
@RockSathi,
DeleteDid the code I posted not work for you? If not, what exactly was the problem?
how to run in a excel
DeleteThanks' sir . its Nice work .
ReplyDeletedate function in hindi
ReplyDeletedate to words in hindi function
ReplyDeleteAfter this macro how i get the result in excel sheet
ReplyDeletehow to run this\ procedure
ReplyDeleteYou 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.http://znajdzlove.pl/
ReplyDeletegolden goose
ReplyDeletekyrie shoes
moncler jackets
michael jordan shoes
birkin bag
How do I make money from playing games and earning
ReplyDeleteThese are งานออนไลน์ the three most popular forms of gambling, and are gri-go.com explained in a very concise and concise manner. 토토 사이트 The most common forms https://access777.com/ of www.jtmhub.com gambling are: