LinkBack URL
About LinkBacks
I copy and paste from a word file to a excel file. But excel file shows date format wrong.
For example;
Word file has 09/10 : 09 refers month, 10 refers year
Excel file : 09 refers day, 10 refers month
How to fix it on excel file?
I copy and paste from a word file to a excel file. But excel file shows date format wrong.
For example;
Word file has 09/10 : 09 refers month, 10 refers year
Excel file : 09 refers day, 10 refers month
How to fix it on excel file?
Excel will interpret 09/10 as 09/10/2015, try typing it in a cell.
How are you copying/pasting?
Do you have an actual date in Word?
If posting code please use code tags, see here.
If that is all you have...2 numbers, /, 2 numbers, excel may be seeing that as text
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Here is the word file. Select, make CTRL+X and CTRL+V on excel.
It's probably your regional settings - check if your computer is on international settings (day/month/year) or American settings (month/day/year).
Edit:
If you don't want to change your settings, use this to convert the wrong date in A1:
Formula:
Please Login or Register to view this content.
then copy the result and 'Paste Values' over A1.
Last edited by Aardigspook; 12-02-2015 at 11:23 AM. Reason: Add formula as other option
Regards,
Aardigspook
I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
You don't need to give me rep if I helped, but a thank-you is nice.
Instead of using CTRL+V, format the range (may as well format entire sheet) you are going to paste to as Text and then goto Paste>Match Destination Formatting to do the paste.
The formula won't work. It turns out 9/15/2015 into 3/9/2016. I need 9/15 (as month and year)
Formula:Please Login or Register to view this content.
Is there a formula to do it?
When pasted into Excel the dates that you have are interpreted as Day/Month or Month/Day depending upon the regional settings of your computer. Pre-formatting the cells in Excel doesn't help unless formatted as text. If the dates are copied and pasted into Text formatted cells in Excel (I used column A) you can enter this in column B to get the 4 digit year.
B1 filled down:
Formula:Please Login or Register to view this content.
C1 filled down:
Formula:Please Login or Register to view this content.
Result
08/12 08/2012 08/12 08/2012 08/12 08/2012 08/12 08/2012 08/12 08/2012 09/13 09/2013 08/12 08/2012 08/12 08/2012 08/12 08/2012
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
Ah, sorry, I mis-read the problem (switching day / month is a common problem on here, so I jumped to that conclusion
If you are starting from scratch (no dates in Excel yet) then format the sheet as text and use newdoverman's solution above.
If you already have a lot of wrongly-formatted dates, try this:
With the list of films in column A and (wrong) dates in column B, put this in C1 and drag down:
Formula:Please Login or Register to view this content.
Then format the cells with the custom format 'mm/yy' (without the inverted commas).
That should do what you want - sorry for my earlier misunderstanding.
Sorry it's taken me a while to reply - I kept being blocked for a technical reason I've now been told how to get round.
The formula won't work. It turns out all years into 2015.Originally Posted by Aardigspook
Ah, sorry, I mis-read the problem (switching day / month is a common problem on here, so I jumped to that conclusion
If you are starting from scratch (no dates in Excel yet) then format the sheet as text and use newdoverman's solution above.
If you already have a lot of wrongly-formatted dates, try this:
With the list of films in column A and (wrong) dates in column B, put this in C1 and drag down:
Formula:Please Login or Register to view this content.
Then format the cells with the custom format 'mm/yy' (without the inverted commas).
That should do what you want - sorry for my earlier misunderstanding.
Sorry it's taken me a while to reply - I kept being blocked for a technical reason I've now been told how to get round.
Your formulas won't work as well. It turns out all years into 2015.When pasted into Excel the dates that you have are interpreted as Day/Month or Month/Day depending upon the regional settings of your computer. Pre-formatting the cells in Excel doesn't help unless formatted as text. If the dates are copied and pasted into Text formatted cells in Excel (I used column A) you can enter this in column B to get the 4 digit year.
B1 filled down:
Formula:Please Login or Register to view this content.
C1 filled down:
Formula:Please Login or Register to view this content.
Result
08/12 08/2012 08/12 08/2012 08/12 08/2012 08/12 08/2012 08/12 08/2012 09/13 09/2013 08/12 08/2012 08/12 08/2012 08/12 08/2012
I uploaded the file.
Here;
That sounds like a really complicated way of doing something rather simple.
The easiest way is to set a custom formatting for the cells you will be pasting into and type mm/yy then paste the info into the cells but paste only the values ignoring the source formatting. (Match destination formatting).
That should sort your problem.
the original file is lost. I can't do it anymore. I must do it on excel.
Last edited by zanshin777; 01-15-2016 at 11:23 AM.
You may need paste first, in the corner an icon will appear and you can select what to paste from that icon. When I'm back in front of my PC properly I'll take a screen shot.
Please note that there will probably not be any text, just icons, when you hover over the icon it will show what the icon does
I'll have a look at it and see what I can do - bit busy tonight so it might not be until tomorrow sometime.
I made it.
1) I extract the day, the month and the year on two adjacent columns with these functions. (DAY, MONTH)
2) The day will be year. I add it "20" with "=20&C2" formula.
3)I put them together with DATE function. I use "1" for days.
4) I copy the real dates with formulas and make paste values to a new column.
5) I delete unnecessary cells.
Thank you very much all for response.
Glad you got it working.
For info only, that's essentially what I've done in the amended file I've just done, though I did it in fewer stages using DATE(DAY(B2),MONTH(B2),1) then copy/paste values. I didn't add the '20' manually because Excel converts two-digit years automatically - if the year is xx00 to xx29, Excel assumes 2000 to 2029 - if the year is xx30 to xx99, it assumes 1930 to 1999 - unless you've changed this setting through Excel options.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks