Copy/pasting a date in text format?
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 |
Copy/pasting a date in text format?
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 |
Copy/pasting a date in text format?
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 |
Copy/pasting a date in text format?
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 |
All times are GMT +1. The time now is 03:41 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com