+ Reply to Thread
Results 1 to 19 of 19

It sets date format wrong when copy and paste

  1. #1
    Forum Contributor
    Join Date
    01-16-2015
    Location
    Istanbul
    MS-Off Ver
    2010
    Posts
    803

    It sets date format wrong when copy and paste

    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?

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,572

    Re: It sets date format wrong when copy and paste

    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.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,776

    Re: It sets date format wrong when copy and paste

    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

  4. #4
    Forum Contributor
    Join Date
    01-16-2015
    Location
    Istanbul
    MS-Off Ver
    2010
    Posts
    803

    Re: It sets date format wrong when copy and paste

    Here is the word file. Select, make CTRL+X and CTRL+V on excel.
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL (COVID allowing)
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,350

    Re: It sets date format wrong when copy and paste

    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: copy to clipboard
    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

    Recently moved house, internationally, during COVID (!) 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.

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,572

    Re: It sets date format wrong when copy and paste

    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.

  7. #7
    Forum Contributor
    Join Date
    01-16-2015
    Location
    Istanbul
    MS-Off Ver
    2010
    Posts
    803

    Re: It sets date format wrong when copy and 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: copy to clipboard
    Please Login or Register  to view this content.


    Is there a formula to do it?

  8. #8
    Forum Contributor
    Join Date
    01-16-2015
    Location
    Istanbul
    MS-Off Ver
    2010
    Posts
    803

    Re: It sets date format wrong when copy and paste


  9. #9
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: It sets date format wrong when copy and paste

    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: copy to clipboard
    Please Login or Register  to view this content.

    C1 filled down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Result
    A
    B
    C
    1
    08/12 08/2012
    01/08/2012
    2
    08/12 08/2012
    01/08/2012
    3
    08/12 08/2012
    01/08/2012
    4
    08/12 08/2012
    01/08/2012
    5
    08/12 08/2012
    01/08/2012
    6
    09/13 09/2013
    01/09/2013
    7
    08/12 08/2012
    01/08/2012
    8
    08/12 08/2012
    01/08/2012
    9
    08/12 08/2012
    01/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

  10. #10
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL (COVID allowing)
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,350

    Re: It sets date format wrong when copy and paste

    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: copy to clipboard
    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.

  11. #11
    Forum Contributor
    Join Date
    01-16-2015
    Location
    Istanbul
    MS-Off Ver
    2010
    Posts
    803

    Re: It sets date format wrong when copy and paste

    Quote Originally Posted by Aardigspook View Post
    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: copy to clipboard
    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.

  12. #12
    Forum Contributor
    Join Date
    01-16-2015
    Location
    Istanbul
    MS-Off Ver
    2010
    Posts
    803

    Re: It sets date format wrong when copy and paste

    Quote Originally Posted by newdoverman View Post
    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: copy to clipboard
    Please Login or Register  to view this content.

    C1 filled down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Result
    A
    B
    C
    1
    08/12 08/2012
    01/08/2012
    2
    08/12 08/2012
    01/08/2012
    3
    08/12 08/2012
    01/08/2012
    4
    08/12 08/2012
    01/08/2012
    5
    08/12 08/2012
    01/08/2012
    6
    09/13 09/2013
    01/09/2013
    7
    08/12 08/2012
    01/08/2012
    8
    08/12 08/2012
    01/08/2012
    9
    08/12 08/2012
    01/08/2012
    Your formulas won't work as well. It turns out all years into 2015.

  13. #13
    Forum Contributor
    Join Date
    01-16-2015
    Location
    Istanbul
    MS-Off Ver
    2010
    Posts
    803

    Re: It sets date format wrong when copy and paste

    I uploaded the file.

    Here;
    Attached Files Attached Files

  14. #14
    Forum Contributor
    Join Date
    02-26-2014
    Location
    Aylsham, Norfolk, UK
    MS-Off Ver
    Excel 365
    Posts
    126

    Re: It sets date format wrong when copy and paste

    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.

  15. #15
    Forum Contributor
    Join Date
    01-16-2015
    Location
    Istanbul
    MS-Off Ver
    2010
    Posts
    803

    Re: It sets date format wrong when copy and paste

    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.

  16. #16
    Forum Contributor
    Join Date
    02-26-2014
    Location
    Aylsham, Norfolk, UK
    MS-Off Ver
    Excel 365
    Posts
    126

    Re: It sets date format wrong when copy and paste

    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

  17. #17
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL (COVID allowing)
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,350

    Re: It sets date format wrong when copy and paste

    Quote Originally Posted by zanshin777 View Post
    I uploaded the file.
    I'll have a look at it and see what I can do - bit busy tonight so it might not be until tomorrow sometime.

  18. #18
    Forum Contributor
    Join Date
    01-16-2015
    Location
    Istanbul
    MS-Off Ver
    2010
    Posts
    803

    Re: It sets date format wrong when copy and paste

    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.

  19. #19
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL (COVID allowing)
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,350

    Re: It sets date format wrong when copy and paste

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Copy Paste each 10 sets Filtered data to different cell desstination
    By pojako08 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-23-2014, 11:51 AM
  2. Copy and paste under the last used row - what's wrong with this code?
    By luke1908 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-24-2014, 04:10 PM
  3. How to copy and paste date but maintaining the date format using macros
    By Wanjama in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-16-2014, 09:03 AM
  4. [SOLVED] Create sets of data (repetitive copy-paste)
    By edarevalo in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-28-2012, 04:56 AM
  5. Copy two data sets, filter it and paste to summary sheet
    By pauldaddyadams in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-03-2012, 07:15 AM
  6. Date format macro and copy paste
    By Libster78 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-07-2010, 11:08 AM
  7. Copy-paste from csv to xls macro > wrong date format
    By Gero in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-30-2009, 06:12 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1