Is there any way to specify that a date entered as say 20/12/08 should
be displayed in Excel 2000 as 'Sat 20 December 2008' please? Even
better, 'Saturday 20th December, 2008'?

--
Terry, East Grinstead, UK

On Sat, 20 Dec 2008 14:58:38 +0000, Terry Pinnell
wrote:

Is there any way to specify that a date entered as say 20/12/08 should
be displayed in Excel 2000 as 'Sat 20 December 2008' please? Even
better, 'Saturday 20th December, 2008'?


Try a Custom cell format

DDD DD\th MMM, YYYY

It will however always display "ht" after the data, like 1th and 2th
instead of 1st and 2nd as you probably want.

Hope this helps / Lars-Åke

Select the range of cells, press Ctrl+1 (numeral one) to open the Format
dialog, and on the Numbers tab select an appropriate Date format, or design
your own.

'Saturday 20th December, 2008' is the result of a format of

dddd dd"th" mmmm, yyyy

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Terry Pinnell" wrote in message
...
Is there any way to specify that a date entered as say 20/12/08 should
be displayed in Excel 2000 as 'Sat 20 December 2008' please? Even
better, 'Saturday 20th December, 2008'?

--
Terry, East Grinstead, UK

One way:

Format/Cells/Number/Custom "ddd dd mmmm yyyy"



In article ,
Terry Pinnell wrote:

Is there any way to specify that a date entered as say 20/12/08 should
be displayed in Excel 2000 as 'Sat 20 December 2008' please? Even
better, 'Saturday 20th December, 2008'?

Note that you'll also see

Monday 01th December, 2008

and

Wednesday 03th December, 2008


using that format.


In article ,
"Jon Peltier" wrote:

'Saturday 20th December, 2008' is the result of a format of

dddd dd"th" mmmm, yyyy

You won't get the indicator (1st, 2nd, 3rd, 4th, ...), but you could try a
custom number format of:

ddd dd mmmm yyyy
or
dddd dd mmmm, yyyy



Terry Pinnell wrote:

Is there any way to specify that a date entered as say 20/12/08 should
be displayed in Excel 2000 as 'Sat 20 December 2008' please? Even
better, 'Saturday 20th December, 2008'?

--
Terry, East Grinstead, UK

--

Dave Peterson

You can't format a cell to display ordinal suffixes (the st, nd, rd, th
ending), but you can do what you want via a formula in another cell.
Assuming your date is in A2, put this formula wherever you want...

=TEXT(A2,"dddd
")&DAY(A2)&MID("thstndrdth",MIN(9,2*RIGHT(DAY(A2)) *(MOD(DAY(A2)-11,100)2)+1),2)&TEXT(A2,"
mmmm, yyyy")

This formula can be copied down or across if needed.

--
Rick (MVP - Excel)


"Terry Pinnell" wrote in message
...
Is there any way to specify that a date entered as say 20/12/08 should
be displayed in Excel 2000 as 'Sat 20 December 2008' please? Even
better, 'Saturday 20th December, 2008'?

--
Terry, East Grinstead, UK

You could do it with event code behind the sheet.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If IsDate(.Value) Then
Select Case Day(.Value)
Case 1, 21, 31
.NumberFormat = "dddd d""st"" mmm, yyyy"
Case 2, 22
.NumberFormat = "dddd d""nd"" mmm, yyyy"
Case 3, 23
.NumberFormat = "dddd d""rd"" mmm, yyyy"
Case 4 To 20, 24 To 30
.NumberFormat = "dddd d""th"" mmm, yyyy"
End Select
End If
End With
End Sub


Gord Dibben MS Excel MVP

On Sat, 20 Dec 2008 14:58:38 +0000, Terry Pinnell
wrote:

Is there any way to specify that a date entered as say 20/12/08 should
be displayed in Excel 2000 as 'Sat 20 December 2008' please? Even
better, 'Saturday 20th December, 2008'?

A minor simplification. Change this statement...

Case 4 To 20, 24 To 30

to this...

Case Else

Also, the OP may want to filter the event for certain cells only...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("C4:H9")) Is Nothing Then Exit Sub
With Target
If IsDate(.Value) Then
Select Case Day(.Value)
Case 1, 21, 31
.NumberFormat = "dddd d""st"" mmm, yyyy"
Case 2, 22
.NumberFormat = "dddd d""nd"" mmm, yyyy"
Case 3, 23
.NumberFormat = "dddd d""rd"" mmm, yyyy"
Case Else
.NumberFormat = "dddd d""th"" mmm, yyyy"
End Select
End If
End With
End Sub

Note to the OP... my example used the range C4:H9 to apply this event code
to; this was accomplished via the Range property call in the first line of
the code... change that range to suit your needs.

--
Rick (MVP - Excel)


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
You could do it with event code behind the sheet.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If IsDate(.Value) Then
Select Case Day(.Value)
Case 1, 21, 31
.NumberFormat = "dddd d""st"" mmm, yyyy"
Case 2, 22
.NumberFormat = "dddd d""nd"" mmm, yyyy"
Case 3, 23
.NumberFormat = "dddd d""rd"" mmm, yyyy"
Case 4 To 20, 24 To 30
.NumberFormat = "dddd d""th"" mmm, yyyy"
End Select
End If
End With
End Sub


Gord Dibben MS Excel MVP

On Sat, 20 Dec 2008 14:58:38 +0000, Terry Pinnell
wrote:

Is there any way to specify that a date entered as say 20/12/08 should
be displayed in Excel 2000 as 'Sat 20 December 2008' please? Even
better, 'Saturday 20th December, 2008'?

On Sat, 20 Dec 2008 14:58:38 +0000, Terry Pinnell
wrote:

Is there any way to specify that a date entered as say 20/12/08 should
be displayed in Excel 2000 as 'Sat 20 December 2008' please? Even
better, 'Saturday 20th December, 2008'?

Since the format of the cell needs to change dynamically depending on it's
contents, you could use an event triggered macro.

One way to do this:

Right click on the sheet tab.

Select View Code and paste the code below into the window that opens.

The code is set so that date entries in column A will be converted to display
in your desired, ordinal format.

=====================================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim sFmt As String
Dim sSuffix As String
Dim c As Range, rg As Range
Const sFmtB As String = "dddd x mmmm, yyyy"
'sets Col A to have ordinal date format
Set rg = Range("A:A")

If Not Intersect(Target, rg) Is Nothing Then
Application.EnableEvents = False
For Each c In Intersect(Target, rg)
If IsDate(c.Value) Then
Select Case Day(c.Value)
Case Is = 1, 21, 31
sSuffix = "st"
Case Is = 2, 22
sSuffix = "nd"
Case Is = 3, 23
sSuffix = "rd"
Case Else
sSuffix = "th"
End Select
sSuffix = """" & sSuffix & """"

sFmt = Replace(sFmtB, "x", "d" & sSuffix)
c.NumberFormat = sFmt
End If
Next c
End If
Application.EnableEvents = True
End Sub
====================================

--ron

JE McGimpsey wrote:

One way:

Format/Cells/Number/Custom "ddd dd mmmm yyyy"



In article ,
Terry Pinnell wrote:

Is there any way to specify that a date entered as say 20/12/08 should
be displayed in Excel 2000 as 'Sat 20 December 2008' please? Even
better, 'Saturday 20th December, 2008'?

Thanks all, much appreciated. I've gone for "ddd dd mmmm yyyy" for the
time being but will try the more complex alternatives later.

--
Terry, East Grinstead, UK