+ Reply to Thread
Results 1 to 14 of 14

Convert Date in DD.MM.YYYY to DD/MM/YYYY format

  1. #1
    Forum Contributor
    Join Date
    07-09-2013
    Location
    Nagpur, India
    MS-Off Ver
    Excel 2010 / 2013
    Posts
    293

    Convert Date in DD.MM.YYYY to DD/MM/YYYY format

    I needed to convert date which is in "DD.MM.YYYY" to date format "DD/MM/YYYY" in cells J3:J400 of my worksheet.

    For Ex.

    To convert date 31.12.2018 to 31/12/2018
    To convert date 01.01.2019 to 01/01/2019
    To convert date 02.01.2019 to 02/01/2019

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

    Re: Convert Date in DD.MM.YYYY to DD/MM/YYYY format

    Select J3:J400, goto Data>Text to columns..., on the 3rd step select DMY from the column Data format drop down and click Finish.
    If posting code please use code tags, see here.

  3. #3
    Forum Contributor
    Join Date
    07-09-2013
    Location
    Nagpur, India
    MS-Off Ver
    Excel 2010 / 2013
    Posts
    293

    Re: Convert Date in DD.MM.YYYY to DD/MM/YYYY format

    Can I do the same using Event macro code for my particular sheet?

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

    Re: Convert Date in DD.MM.YYYY to DD/MM/YYYY format

    What event would trigger the code?

  5. #5
    Forum Contributor
    Join Date
    07-09-2013
    Location
    Nagpur, India
    MS-Off Ver
    Excel 2010 / 2013
    Posts
    293

    Re: Convert Date in DD.MM.YYYY to DD/MM/YYYY format

    If user type or copy paste date as "dd.mm.yyyy" format in column J3 to J400 then it should auto convert into "dd/mm/yyyy" format.

  6. #6
    Valued Forum Contributor mohan.r1980's Avatar
    Join Date
    09-18-2010
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2010 (windows7)
    Posts
    729

    Re: Convert Date in DD.MM.YYYY to DD/MM/YYYY format

    try
    Please Login or Register  to view this content.
    Regards,
    MohanS


    "Perfection is not attainable, but if we chase perfection we can catch excellence." - Vince Lombardi

    You can simply say thanks by clicking "*Add Reputation" icon

  7. #7
    Forum Expert nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    Office 365 Windows 10
    Posts
    2,286

    Re: Convert Date in DD.MM.YYYY to DD/MM/YYYY format

    Please Login or Register  to view this content.
    for range you specified
    Last edited by nigelog; 01-16-2019 at 08:42 AM.

  8. #8
    Forum Contributor
    Join Date
    07-09-2013
    Location
    Nagpur, India
    MS-Off Ver
    Excel 2010 / 2013
    Posts
    293

    Re: Convert Date in DD.MM.YYYY to DD/MM/YYYY format

    Quote Originally Posted by nigelog View Post
    Please Login or Register  to view this content.
    for range you specified
    This code converting "dd.mm.yyyy" into "mm/dd/yyyy" instead of "dd/mm/yyyy".

    For example if i type 02.01.2019 then the code is converting it into 01/02/2019 (US date format) instead of 02/01/2019.

  9. #9
    Forum Expert nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    Office 365 Windows 10
    Posts
    2,286

    Re: Convert Date in DD.MM.YYYY to DD/MM/YYYY format

    Change
    Please Login or Register  to view this content.

  10. #10
    Forum Contributor
    Join Date
    07-09-2013
    Location
    Nagpur, India
    MS-Off Ver
    Excel 2010 / 2013
    Posts
    293

    Re: Convert Date in DD.MM.YYYY to DD/MM/YYYY format

    Ok. It worked. Thanks.

  11. #11
    Forum Expert nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    Office 365 Windows 10
    Posts
    2,286

    Re: Convert Date in DD.MM.YYYY to DD/MM/YYYY format

    Glad you got it sorted. Thanks for the rep.

  12. #12
    Forum Contributor
    Join Date
    07-09-2013
    Location
    Nagpur, India
    MS-Off Ver
    Excel 2010 / 2013
    Posts
    293

    Re: Convert Date in DD.MM.YYYY to DD/MM/YYYY format

    Quote Originally Posted by Norie View Post
    Select J3:J400, goto Data>Text to columns..., on the 3rd step select DMY from the column Data format drop down and click Finish.
    Can I do that on Only those cells in column J in which the date is in dd.mm.yyyy format.
    I don't want to convert text to columns in column J in which the date is in other format except dd.mm.yyyy.

    I needed to add that settings in my below code.

    Please Login or Register  to view this content.

  13. #13
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: Convert Date in DD.MM.YYYY to DD/MM/YYYY format

    what other formats do dates appear in? Perhaps something like below, it assumes the cell contains 10 characters and 2 of these are "."


    Please Login or Register  to view this content.
    Last edited by davsth; 01-21-2019 at 05:19 AM.

  14. #14
    Forum Contributor
    Join Date
    07-09-2013
    Location
    Nagpur, India
    MS-Off Ver
    Excel 2010 / 2013
    Posts
    293

    Re: Convert Date in DD.MM.YYYY to DD/MM/YYYY format

    Quote Originally Posted by davsth View Post
    what other formats do dates appear in? Perhaps something like below, it assumes the cell contains 10 characters and 2 of these are "."


    Please Login or Register  to view this content.
    When i'm copy and pasting data to my sheet, the code is giving type mismatch in
    Please Login or Register  to view this content.

+ 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. Convert Date format from Text format reading m/d/yyyy to dd/mm/yyyy
    By bridge4444 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-23-2017, 11:22 PM
  2. [SOLVED] Issue in macro to convert date from DD-MON-YYYY into YYYY-MON-DD
    By abraham30 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-20-2014, 09:17 AM
  3. [SOLVED] How to Convert date format ( DD/MM/YYYY) into MM/YYYY
    By PRADEEPB270 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-04-2014, 07:25 AM
  4. [SOLVED] convert MM/DD/YYYY to DD/MM/YYYY while the data format is text
    By Vogelmann in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-18-2012, 02:43 PM
  5. [SOLVED] Imported data contains strings dd.mm.yyyy how can I convert to date format dd/mm/yyyy inVB
    By Boormo in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 09-05-2012, 05:48 PM
  6. Macro to convert dates format from yyyy mm dd to yyyy-mm-dd
    By Gators in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 02-26-2009, 02:23 PM
  7. opening excel file -> date format problem: DD/MM/YYYY vs MM/DD/YYYY
    By yung in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-18-2005, 09:06 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