View Single Post
  #6   Report Post  
Posted to microsoft.public.excel.misc
Terry Pinnell[_4_] Terry Pinnell[_4_] is offline
external usenet poster
 
Posts: 192
Default Customising date format

Claus Busch wrote:

Hi Terry,

Am Sun, 21 Aug 2016 17:41:07 +0100 schrieb Terry Pinnell:

After entering a date as say 2/8/16 it's easy to format it to become
Tuesday 2 Aug 2016 by using 'dddd d mmmm yyyy'. But is it possible to
refine dates further to
Tuesday 2nd August 2016
Wednesday 3rd August 2016
etc ?


if you insert the dates in column A then try this Worksheet_Change
event:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A:A")) Is Nothing Or _
Target.Count 1 Then Exit Sub

If IsDate(Target) Then
Select Case Day(Target)
Case 1, 11, 21, 31
Target.NumberFormat = "dddd d""st"" mmmm yyyy"
Case 2, 22
Target.NumberFormat = "dddd d""nd"" mmmm yyyy"
Case 3, 23
Target.NumberFormat = "dddd d""rd"" mmmm yyyy"
Case Else
Target.NumberFormat = "dddd d""th"" mmmm yyyy"
End Select
End If
End Sub


Regards
Claus B.


Thanks both.

In no-brainer terms, could you describe the steps I should take after
copying that code to the clipboard please? Is that just the same as a
macro?

--
Terry, East Grinstead, UK