+ Reply to Thread
Results 1 to 15 of 15

changing the date format

  1. 03-24-2015, 06:33 AM #1
    EmileEA
    EmileEA is offline
    Registered User
    Join Date
    01-13-2015
    Location
    Amsterdam
    MS-Off Ver
    2013
    Posts
    16

    changing the date format

    Hi Gurus,

    I'm currently struggling with the following problem:

    I have a date in the following format: 25-JAN-2013
    I want to convert this to: 20130125

    Could anyone help me with a solution?

    Many thanks!

    Emile
    Reply With QuoteRegister To Reply
  2. 03-24-2015, 06:37 AM #2
    Special-K
    Special-K is offline
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,077

    Re: changing the date format

    Does this work?

    =TEXT(PROPER(A1)+0,"yyyymmdd")
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
    Reply With QuoteRegister To Reply
  3. 03-24-2015, 06:37 AM #3
    NeedForExcel
    NeedForExcel is offline
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,879

    Re: changing the date format

    Hi,

    On the Cell, Press Ctrl+1 to open Format Cells Dialogue Box, Select the last Option "Custom" and Paste this - yyyymmdd

    Click OK..
    Cheers!
    Deep Dave
    Reply With QuoteRegister To Reply
  4. 03-24-2015, 06:47 AM #4
    EmileEA
    EmileEA is offline
    Registered User
    Join Date
    01-13-2015
    Location
    Amsterdam
    MS-Off Ver
    2013
    Posts
    16

    Re: changing the date format

    Quote Originally Posted by Special-K View Post
    Does this work?

    =TEXT(PROPER(A1)+0,"yyyymmdd")
    Hi Special-K thanks for the quick reply

    By entering your formula it returned yyyy0125,, very close but for some reason the yyyy did not change into 2013.
    Reply With QuoteRegister To Reply
  5. 03-24-2015, 06:49 AM #5
    EmileEA
    EmileEA is offline
    Registered User
    Join Date
    01-13-2015
    Location
    Amsterdam
    MS-Off Ver
    2013
    Posts
    16

    Re: changing the date format

    Quote Originally Posted by msexcelathome View Post
    Hi,

    On the Cell, Press Ctrl+1 to open Format Cells Dialogue Box, Select the last Option "Custom" and Paste this - yyyymmdd

    Click OK..
    Hi,

    Sam problem as with Special-K his answer. This changes the format to yyyy0125.
    Reply With QuoteRegister To Reply
  6. 03-24-2015, 06:51 AM #6
    NeedForExcel
    NeedForExcel is offline
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,879

    Re: changing the date format

    Seems as if your dates aren't entered correctly.. Can you attach a sample workbook?
    Reply With QuoteRegister To Reply
  7. 03-24-2015, 08:15 AM #7
    EmileEA
    EmileEA is offline
    Registered User
    Join Date
    01-13-2015
    Location
    Amsterdam
    MS-Off Ver
    2013
    Posts
    16

    Re: changing the date format

    Hi,
    Please find enclosed the excel file
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 03-24-2015, 08:18 AM #8
    Special-K
    Special-K is offline
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,077

    Re: changing the date format

    My formula works perfectly on the data posted.
    Reply With QuoteRegister To Reply
  9. 03-24-2015, 08:23 AM #9
    EmileEA
    EmileEA is offline
    Registered User
    Join Date
    01-13-2015
    Location
    Amsterdam
    MS-Off Ver
    2013
    Posts
    16

    Re: changing the date format

    When I do it, it returns yyyy0125.. See in the attached file. Am I doing something wrong or?
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  10. 03-24-2015, 08:24 AM #10
    NeedForExcel
    NeedForExcel is offline
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,879

    Re: changing the date format

    Hi, your dates are not in date format.. Hence my solution will not work..
    Reply With QuoteRegister To Reply
  11. 03-24-2015, 08:30 AM #11
    EmileEA
    EmileEA is offline
    Registered User
    Join Date
    01-13-2015
    Location
    Amsterdam
    MS-Off Ver
    2013
    Posts
    16

    Re: changing the date format

    Quote Originally Posted by msexcelathome View Post
    Hi, your dates are not in date format.. Hence my solution will not work..
    If I first change the format to date and than to Ctrl+1 Custom yyyymmdd I also get yyyy0125
    Reply With QuoteRegister To Reply
  12. 03-24-2015, 08:32 AM #12
    Special-K
    Special-K is offline
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,077

    Re: changing the date format

    in A3

    If you hit F2 to edit then press return it produces the correct date
    Not sure why???
    Reply With QuoteRegister To Reply
  13. 03-24-2015, 08:33 AM #13
    EmileEA
    EmileEA is offline
    Registered User
    Join Date
    01-13-2015
    Location
    Amsterdam
    MS-Off Ver
    2013
    Posts
    16

    Re: changing the date format

    Wow.. Just found the solution. How silly. I'm using a computer with Dutch Excel.. Year means Jaar.. The right format was jjjjmmdd. Both of your soluions worked when I used J instead of Y.

    So many thanks for your help! <3
    Reply With QuoteRegister To Reply
  14. 03-24-2015, 08:34 AM #14
    Special-K
    Special-K is offline
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,077

    Re: changing the date format

    Good spot :-)
    Reply With QuoteRegister To Reply
  15. 03-24-2015, 08:35 AM #15
    NeedForExcel
    NeedForExcel is offline
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,879

    Re: changing the date format

    Haha! Great..

    Please mark the thread as solved if you have your answer..
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Formula for Changing date format 3-12-13 to format d-mm-yy.
    By UNAB in forum Excel General
    Replies: 2
    Last Post: 04-08-2013, 03:52 PM
  2. [SOLVED] Changing Multiple sheet names by wk ending date for yr but need specific name/date format
    By kedeling in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-06-2013, 02:40 PM
  3. Need help changing date to a sortable date format
    By slicksilver79 in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 12-15-2011, 10:44 AM
  4. Formula required for date without changing the date format
    By suryaprasad in forum Excel General
    Replies: 1
    Last Post: 01-28-2009, 08:04 AM
  5. Changing date serial numbers to date format
    By rdunne in forum Excel General
    Replies: 1
    Last Post: 04-14-2005, 08:06 AM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1