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

Hi Terry,

Am Mon, 08 Jan 2018 10:22:14 +0000 schrieb Terry Pinnell:

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.

when I copy and paste the date with this format it looks the same in
Editor and Word. The format is copied as well.
If you don't have to calculate with the dates in excel you can change
them to text:

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
rngC = rngC.Text
Next
End Sub


Regards
Claus B.
--
Windows10
Office 2016

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.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

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