GS wrote:

Are you around Claus,?

After your patient help some 18 months ago I have been using the
following formula (function?):

Sub FormatDate()
' October 2016, from Claus
' If applied to 07/01/2018 (any cell) it is replaced by Sunday 7th
January 2018
' Target is formatted dd/mm/yyyy so could enter 7/1/18
' But how to paste the result elsewhere, in its text form?

Dim rngC As Range

For Each rngC In Selection
If IsDate(rngC) Then
Select Case Day(rngC)
Case 1, 21, 31
rngC.NumberFormat = "dddd d""st"" mmmm yyyy"
Case 2, 22
rngC.NumberFormat = "dddd d""nd"" mmmm yyyy"
Case 3, 23
rngC.NumberFormat = "dddd d""rd"" mmmm yyyy"
Case Else
rngC.NumberFormat = "dddd d""th"" mmmm yyyy"
End Select
End If
Next
End Sub


Is it possible to edit that so that I could then simply copy the result
and paste it elsewhere, outside Excel, in its text format? At present it
pastes the date in raw number form.

Terry, East Grinstead, UK

If the cell is set to date format, copy/paste results the same as what's
displayed in the cell.

Claus, Garry: Thanks both. Sorry I wasn't clear enough. By 'elsewhere' I
meant 'elsewhere, outside Excel, such as my text editor'.

That one extra line
rngC = rngC.Text
setting the result to text does the job nicely, thanks! So simple now
that I see it ;-)


Terry, East Grinstead, UK