+ Reply to Thread
Results 1 to 8 of 8

Converting an ugly Date Time cell to an Excel readable one

  1. #1
    Forum Contributor
    Join Date
    07-22-2013
    Location
    london
    MS-Off Ver
    Excel 2013
    Posts
    107

    Converting an ugly Date Time cell to an Excel readable one

    Hello all

    We got a column like this:
    (excel cannot recognize this as date/time)

    18 Apr 2021 at 17:33
    22 Apr 2021 at 12:00
    18 Apr 2021 at 17:55
    18 Apr 2021 at 17:47
    21 Apr 2021 at 00:00
    19 Apr 2021 at 11:00
    21 Apr 2021 at 05:00

    and we want to turn it readable.

    How do we do it?

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,501

    Re: Converting an ugly Date Time cell to an Excel readable one

    Data | Text to Columns | Fixed width will get it separated out for you.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,042

    Re: Converting an ugly Date Time cell to an Excel readable one

    A
    B
    1
    18 Apr 2021 at 17:33
    18/04/2021 17:33
    2
    22 Apr 2021 at 12:00
    22/04/2021 12:00


    A
    B
    1
    18 Apr 2021 at 17:33
    =DATEVALUE(TEXT(SUBSTITUTE(A1,"at",""),"dd mmm yyyy"))+TIMEVALUE(RIGHT(A1,5))
    2
    22 Apr 2021 at 12:00
    =DATEVALUE(TEXT(SUBSTITUTE(A2,"at",""),"dd mmm yyyy"))+TIMEVALUE(RIGHT(A2,5))
    Happy with my answer * Add Reputation.
    If You are happy with solution, please use Thread tools and mark thread as SOLVED.

  4. #4
    Forum Expert
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    2,389

    Re: Converting an ugly Date Time cell to an Excel readable one

    =SUBSTITUTE(A1," at "," ")
    in B1 - copy down - then format dd mm yyyy hh:mm
    seems to work - tested taking two dates away from each other with format [h];mm
    torachan.

  5. #5
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,042

    Re: Converting an ugly Date Time cell to an Excel readable one

    With this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    even if it looks like date/time there is still text.


    A
    B
    C
    1
    18 Apr 2021 at 17:33 18 Apr 2021 17:33
    FALSE

    A
    B
    C
    1
    18 Apr 2021 at 17:33 =SUBSTITUTE(A1," at "," ")
    =ISNUMBER(B1)
    Last edited by KOKOSEK; 04-19-2021 at 08:43 AM.

  6. #6
    Forum Contributor
    Join Date
    07-22-2013
    Location
    london
    MS-Off Ver
    Excel 2013
    Posts
    107

    Re: Converting an ugly Date Time cell to an Excel readable one

    Awesome KOKOSEK..
    Thank you!

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,501

    Re: Converting an ugly Date Time cell to an Excel readable one

    Administrative Note

    ... please don't ignore contributors to your thread - acknowledge all solutions offered, even if they don't meet your requirements. Thanks.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

  8. #8
    Forum Expert
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    2,389

    Re: Converting an ugly Date Time cell to an Excel readable one

    @KOKOSEK, although it does not checkout as a number it still works - see attached sheet
    Also if you take two date/times away from each other a number is produced and when formatted [h]:mm produces the correct difference.
    I guess when is a number not a number ?????
    torachan.
    Attached Files Attached Files

+ 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. [SOLVED] Need to convert Excel readable Date and Time format
    By pramoth.u in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 06-06-2017, 01:27 AM
  2. Replies: 4
    Last Post: 02-15-2017, 08:00 AM
  3. Replies: 3
    Last Post: 03-22-2016, 08:20 AM
  4. Converting a julian date and time to standard date in excel 2010
    By Munkle555 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-22-2015, 10:49 AM
  5. Convert Unix timestamp to Readable Date/time
    By Emily in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-26-2006, 08:00 PM
  6. [SOLVED] Converting a custom date to readable text
    By Crossed eyes in forum Excel General
    Replies: 2
    Last Post: 12-29-2005, 03:26 PM
  7. [SOLVED] converting PDF file to readable excel format
    By TNMAN in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-27-2005, 09:06 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1