+ Reply to Thread
Results 1 to 10 of 10

How do I convert date in text format to date format

  1. 02-15-2019, 01:07 PM #1
    sharki69
    sharki69 is offline
    Registered User
    Join Date
    02-04-2010
    Location
    usa
    MS-Off Ver
    Excel 2007
    Posts
    22

    How do I convert date in text format to date format

    Hello,

    I have column A with date formatted as text (see attached). I need to convert that column to date format (mm/dd/yyyy only) so I will be able to group the months in pivot table.

    Here are samples of the dates (format as text)

    Dec 8, 2018 5:36:21 AM PST
    Dec 11, 2018 11:23:12 AM PST
    Dec 3, 2018 10:07:38 AM PST
    Dec 10, 2018 6:32:02 AM PST


    Any help will be appreciated

    Thanks
    Attached Files Attached Files
    Last edited by sharki69; 02-15-2019 at 01:09 PM.
    Reply With QuoteRegister To Reply
  2. 02-15-2019, 02:49 PM #2
    XLent
    XLent is offline
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: How do I convert date in text format to date format

    If you're running on a US regional setting then I suspect below would work for you:

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  3. 02-15-2019, 02:57 PM #3
    etaf
    etaf is offline
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,739

    Re: How do I convert date in text format to date format

    try
    =DATEVALUE(((TRIM(MID(A2,SEARCH(",",A2,1)-2,2))&"-"&LEFT(A2,3)&"-"&MID(A2,SEARCH(",",A2,1)+2,4))))

    Edit, I'm on UK dates
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
    Reply With QuoteRegister To Reply
  4. 02-15-2019, 03:01 PM #4
    dosydos
    dosydos is offline
    Forum Expert dosydos's Avatar
    Join Date
    12-09-2015
    Location
    Massachusetts
    MS-Off Ver
    365(PC) V:2308
    Posts
    1,472

    Re: How do I convert date in text format to date format

    you could also try this
    =DATE(MID(A2,FIND(",",A2)+2,4),MONTH(LEFT(A2,3)&" 1"),MID(A2,5,FIND(",",A2)-5))
    Reply With QuoteRegister To Reply
  5. 02-15-2019, 04:17 PM #5
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: How do I convert date in text format to date format

    And another.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Custom format "mm/dd/yyyy".

    To include time ...
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Dave
    Reply With QuoteRegister To Reply
  6. 02-15-2019, 04:48 PM #6
    sharki69
    sharki69 is offline
    Registered User
    Join Date
    02-04-2010
    Location
    usa
    MS-Off Ver
    Excel 2007
    Posts
    22

    Re: How do I convert date in text format to date format

    Quote Originally Posted by XLent View Post
    If you're running on a US regional setting then I suspect below would work for you:

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Worked like a charm... Thanks!
    Reply With QuoteRegister To Reply
  7. 02-15-2019, 04:49 PM #7
    sharki69
    sharki69 is offline
    Registered User
    Join Date
    02-04-2010
    Location
    usa
    MS-Off Ver
    Excel 2007
    Posts
    22

    Re: How do I convert date in text format to date format

    Quote Originally Posted by etaf View Post
    try
    =DATEVALUE(((TRIM(MID(A2,SEARCH(",",A2,1)-2,2))&"-"&LEFT(A2,3)&"-"&MID(A2,SEARCH(",",A2,1)+2,4))))

    Edit, I'm on UK dates
    Also worked! Thanks
    Reply With QuoteRegister To Reply
  8. 02-15-2019, 04:50 PM #8
    sharki69
    sharki69 is offline
    Registered User
    Join Date
    02-04-2010
    Location
    usa
    MS-Off Ver
    Excel 2007
    Posts
    22

    Re: How do I convert date in text format to date format

    Quote Originally Posted by dosydos View Post
    you could also try this
    =DATE(MID(A2,FIND(",",A2)+2,4),MONTH(LEFT(A2,3)&" 1"),MID(A2,5,FIND(",",A2)-5))
    BOOM! you guys rock
    Reply With QuoteRegister To Reply
  9. 02-15-2019, 04:51 PM #9
    sharki69
    sharki69 is offline
    Registered User
    Join Date
    02-04-2010
    Location
    usa
    MS-Off Ver
    Excel 2007
    Posts
    22

    Re: How do I convert date in text format to date format

    Quote Originally Posted by FlameRetired View Post
    And another.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Custom format "mm/dd/yyyy".

    To include time ...
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Great! thanks so much
    Reply With QuoteRegister To Reply
  10. 02-15-2019, 05:19 PM #10
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: How do I convert date in text format to date format

    You are welcome.

    Glad to help. Thank you for feedback, kind words, added rep and marking this thread Solved.
    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] Convert text date in to a date format excel can read
    By BC TIPPING in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 12-03-2017, 02:49 AM
  2. [SOLVED] How to convert exported text date to desired date format with excel vba.
    By sktneer in forum Excel Programming / VBA / Macros
    Replies: 25
    Last Post: 10-03-2013, 01:08 AM
  3. Convert date and time in serial number format to text format
    By nda13112 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-11-2013, 02:45 PM
  4. Cannot convert date (which is in text format) to date in numeric format
    By geniuspro in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 10-19-2012, 09:21 AM
  5. Need Formula/VBA Code to convert date in String Format to Normal Date format
    By Karthik Sen in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-18-2012, 04:54 AM
  6. Convert a Date Format to a text format
    By ADArnold in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-08-2008, 08:27 AM
  7. Help: How do I convert a text date into a real date format
    By japorms in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-02-2006, 01:36 PM

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