• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 633
  • Last Modified:

How can I Copy & Paste a Date in a cell and leave only the day month in the cell.

I'm trying to copy and paste a date in a cell showing only the day and month. I do not want the year in the cell.
I have Copy & Paste special values only this returns the full date while showing only the day month eg 06 Jun
I have tried a macro & formula with the same results or showing the formula.
None othe above is satisfactory,
One method that works is to copy & paste into a notebook then copy & paste into a cell.

Sample:
Original Details
Displayed = 6 Jun 1989
Shown in the cell = 6 Jun

Required Details
Displayed = 6 Jun
Shown in the cell = 6 Jun

Has anyone got any ideas.

Thanks
0
user2073
Asked:
user2073
1 Solution
 
Anthony BerenguelCommented:
Hello,

Would it be unsatisfactory if the cell contained the date, but only displayed the month and the day? Because if that is not unsatisfactory you could solve  your problem with custom formatting.
0
 
AmickCommented:
Precede the date with an apostrophe

'06 Jun

This will display as 06 Jun and contain 06 Jun, but without a year, date manipulations will have to be done through an intermediary cell.
0
 
user2073Author Commented:
YES, it would be unsatisfactory for the cell to contain a date and display the day and month.

I ONLY need the the day and month because other process require the column of data to be filtered and other requirements.

When I us the apostrophe before the 06 Jun shown as '06 Jun it works if the date is written in that format but the conversion is from another worksheet where the date is formatted from older records.

Thanks for the thoughts, your assistence is appreciated but didn't resolve this problem.

THANKS
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
WotanAUCommented:
Better off to add an additional column and use a formula to conver the output. Once the output is converted, you could then copy that without issue.
0
 
user2073Author Commented:
I have assembled a macro which works in a similiar process that has been mentioned above. The macro is shown below. At this time I have the date in cell ("K3") then select any empty cell nearby, Then run the macro and all works.

Sub TestToGo()
' This macro changes Date SAMPLE: (06/07/1985 to 6 Jul) Format using a nominated cell.

    ActiveCell.Select
    ActiveCell.FormulaR1C1 = _
        "=CONCATENATE(DAY(R3C11),""  "",TEXT((R3C11),""mmmm""))"
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
End Sub      

My Question would be can this code be changed to run on the activecell and change the data in the same cell.

Any assistence would be appreciated.

THANKS
0
 
WotanAUCommented:
I can't see why not. You could read the cell value into a variable, use the concatenate to convert it, then write it back to the active cell. Obviously in that instance the concatenate would refer to the variable instead of the cell directly.
0
 
user2073Author Commented:
Great you have supported my thoughts. My problem is the how.
My limited knowledge has let me down, sorry.
I have tried  a few things but could not achieve the results, can please help me.

THANKS
0
 
WotanAUCommented:
No problem. Here's the code

    
ActiveCell.Select
tmpval = ActiveCell.Value
ActiveCell.Value = CStr(Chr(13) & Day(tmpval) & " " & MonthName(Month(tmpval)))

Open in new window

0
 
user2073Author Commented:
Your code is a lot simpler than the recorded method I use to develope my code.
Yes, it works perfectly, Thank you very much.

I just wish to have the knowledge in coding that you have.

THANKS a LOT
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now