Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Date Formats Available
Hi,
I was wondering if there was a date format that anyone was aware of that would make the date appear as such: Entered in as 30/12/1979 Displayed as 30th December, 1979 Thanks in Advance, RTANSW -- Message posted via http://www.officekb.com |
#2
|
|||
|
|||
You'd need a VBA event macro to do this.
If you can live with 30 December, 1979 then Format/Cells/Number/Custom dd mmmm, yyyy will do. In article , "RTANSW via OfficeKB.com" wrote: I was wondering if there was a date format that anyone was aware of that would make the date appear as such: Entered in as 30/12/1979 Displayed as 30th December, 1979 |
#3
|
|||
|
|||
On Tue, 31 May 2005 23:01:52 GMT, "RTANSW via OfficeKB.com"
wrote: Hi, I was wondering if there was a date format that anyone was aware of that would make the date appear as such: Entered in as 30/12/1979 Displayed as 30th December, 1979 Thanks in Advance, RTANSW There is no specific format; you would have to change the format each time you entered a date, to use the appropriate ordinal number. You could use an event triggered macro. Let us say, for example, that your date is in A1. Right click the worksheet tab; select View Code, and paste the code below into the window that opens. Then enter a date, or a formula returning a date, into A1. This could be made more efficient, depending on the details of your data entry. ================================= Private Sub Worksheet_Change(ByVal Target As Range) Dim Suffix As String Dim Dt As Range Set Dt = Range("A1") If Not IsNumeric(Dt) And Not IsDate(Dt) Then Exit Sub 'Dates returned from functions, like TODAY(), will fail IsDate Select Case Day(Dt) Case Is = 1, 21, 31 Suffix = "st" Case Is = 2, 22 Suffix = "nd" Case Is = 3, 23 Suffix = "rd" Case Else Suffix = "th" End Select Dt.NumberFormat = "d""" & Suffix & """ mmmm, yyyy" End Sub ================================ --ron |
#4
|
|||
|
|||
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reading Date Formats e.g 20050801 (yyyy/mm/dd) | Excel Discussion (Misc queries) | |||
Changing Cell formats to date fields automatically | Excel Worksheet Functions | |||
excel date formats changed | Excel Worksheet Functions | |||
Seed date formats to different year in different cells | Excel Discussion (Misc queries) | |||
troubleshooting date formats | Excel Discussion (Misc queries) |