+ Reply to Thread
Results 1 to 12 of 12

Convert Text Date/Timestamp to Recognizable date?

  1. #1
    Forum Contributor
    Join Date
    03-15-2010
    Location
    Forney, TX
    MS-Off Ver
    Excel 365, Excel 2016
    Posts
    595

    Convert Text Date/Timestamp to Recognizable date?

    I have a data report that has a date/timestamp like this "Sep 11 2021 5:45AM" and excel doesn't recognize this as a date and timestamp. How can I quickly convert this column to be a proper date/timestamp?

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    19,300

    Re: Convert Text Date/Timestamp to Recognizable date?

    Find & Replace all

    [space]2021
    to
    ,[space]2021

    and

    AM
    to
    [space]AM

    and

    PM
    to
    [space]PM

    Where I used [space] is just a space. I spelled it out to make it clearer what is there.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Forum Contributor
    Join Date
    03-15-2010
    Location
    Forney, TX
    MS-Off Ver
    Excel 365, Excel 2016
    Posts
    595

    Re: Convert Text Date/Timestamp to Recognizable date?

    Quote Originally Posted by 6StringJazzer View Post
    Find & Replace all

    [space]2021
    to
    ,[space]2021

    and

    AM
    to
    [space]AM

    and

    PM
    to
    [space]PM

    Where I used [space] is just a space. I spelled it out to make it clearer what is there.
    I didn't think to try that, it's 800k rows soooo it might be a little slow but gonna give it a shot.

  4. #4
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010, 2019
    Posts
    2,049

    Re: Convert Text Date/Timestamp to Recognizable date?

    Quote Originally Posted by NewYears1978 View Post
    I didn't think to try that, it's 800k rows soooo it might be a little slow but gonna give it a shot.
    I am not sure if you were thinking 6StringJazzer was suggesting something else for you to do but his suggestion was for you to use Excel's Find/Replace dialog box (CTRL+H) to do the two replacements (one at a time of course)... that should not be all that slow.

  5. #5
    Registered User
    Join Date
    09-03-2021
    Location
    South Carolina
    MS-Off Ver
    Office 365
    Posts
    7

    Re: Convert Text Date/Timestamp to Recognizable date?

    As a side-note, be sure you don't have other text within the sheet that might contain the letters "am" in the text that would be affected by find and replace. That would certainly be annoying to have to fix! haha

  6. #6
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010, 2019
    Posts
    2,049

    Re: Convert Text Date/Timestamp to Recognizable date?

    If that is a possibility for the OP, then he should click the "Options>>" button in the dialog and put a checkmark in the Match case" checkbox.

  7. #7
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    19,300

    Re: Convert Text Date/Timestamp to Recognizable date?

    Sounds like these are all in one column so you can select the column first, then the Find & Replace will apply only to that column.

  8. #8
    Valued Forum Contributor
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016
    Posts
    635

    Re: Convert Text Date/Timestamp to Recognizable date?

    Maybe more slower.

    =DATE(MID(A1,8,4),(FIND(LEFT(A1,3),A1)+2)/3,MID(A1,5,2))+TIME(TRIM(MID(A1,12,2)),MID(A1,15,2),0)+IF(RIGHT(A1,2)="PM",TIME(12,0,0),0)

    Regards.
    My English is very poor, so please be patient >_<"

  9. #9
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010, 2019
    Posts
    2,049

    Re: Convert Text Date/Timestamp to Recognizable date?

    Since the OP lives in the US, this formula should work...
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  10. #10
    Forum Contributor
    Join Date
    03-15-2010
    Location
    Forney, TX
    MS-Off Ver
    Excel 365, Excel 2016
    Posts
    595

    Re: Convert Text Date/Timestamp to Recognizable date?

    Quote Originally Posted by 6StringJazzer View Post
    Sounds like these are all in one column so you can select the column first, then the Find & Replace will apply only to that column.
    This is the solution I used.

    I have to do this task at the start of every report so it's kind of tedious but not that big of deal, I will probably write macros to do this.

  11. #11
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    19,300

    Re: Convert Text Date/Timestamp to Recognizable date?

    Where is this data coming from in the first place? Is it possible to address it at the source?

  12. #12
    Forum Contributor
    Join Date
    03-15-2010
    Location
    Forney, TX
    MS-Off Ver
    Excel 365, Excel 2016
    Posts
    595

    Re: Convert Text Date/Timestamp to Recognizable date?

    Quote Originally Posted by 6StringJazzer View Post
    Where is this data coming from in the first place? Is it possible to address it at the source?
    SQL database and I don't have access to it, but I did get with someone to change the queries to fix this formatting so this should be fixed soon =D For now the find replace worked well and got my project finished =D

    Appreciate ya!

+ 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. Replies: 2
    Last Post: 06-09-2018, 11:21 AM
  2. Replies: 3
    Last Post: 06-02-2018, 02:17 PM
  3. Convert Timestamp to date.
    By brent_milne in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-09-2015, 01:31 PM
  4. Convert timestamp to date
    By brent_milne in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 05-29-2015, 10:02 PM
  5. [SOLVED] VBA - Convert Date/Timestamp into American Date and Military Timestamp
    By ryanmorris in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-01-2015, 03:43 PM
  6. Convert Jan/07/2013 11:55:43 AM to a recognizable DATE
    By dynastarsic in forum Excel General
    Replies: 5
    Last Post: 03-14-2014, 09:29 AM
  7. [SOLVED] Convert a cell with a timestamp in it to just the date to compare to another date
    By Tanyab in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-17-2013, 05:41 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