+ Reply to Thread
Results 1 to 6 of 6

convert lenghty text to date

  1. #1
    Registered User
    Join Date
    09-20-2010
    Location
    islamabad
    MS-Off Ver
    Excel 2016
    Posts
    28

    convert lenghty text to date

    Dear folks I tried the following formula to cacluate the date and time but currently its only showing correct date with wrong time how to fix it

    Monday, June 15, 2020 10:08:12 AM forumula I used =DATEVALUE(MID(E2,FIND(",",E2)+2,99)) then it convert in digit 6/15/20 12:00 AM
    Thursday, June 18, 2020 4:12:57 PM` 6/18/20 12:00 AM



    Time is always showing 12:00 AM how to fix it ?
    Attached Files Attached Files
    Last edited by esaji; 07-01-2020 at 04:37 AM.

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    UK
    MS-Off Ver
    various
    Posts
    1,766

    Re: convert lenghty text to date

    If your DATEVALUE calc is working ok in Pakistan, (wouldn't here in UK), then I would suggest just using below:

    =TRIM(REPLACE(A2,1,FIND(",",A2&","),""))+0

    that should generate a date time value with time being the decimal value

    if you want to isolate the time on it's own you can wrap the above in a MOD call - i.e. MOD([date time],1)

  3. #3
    Registered User
    Join Date
    09-20-2010
    Location
    islamabad
    MS-Off Ver
    Excel 2016
    Posts
    28

    Re: convert lenghty text to date

    XLent It work like a charm one more favour I want to understand the logic of your formula can you please shed few lines how its working

  4. #4
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    UK
    MS-Off Ver
    various
    Posts
    1,766

    Re: convert lenghty text to date

    You would probably find that if you adapted your own approach it would work just as well:

    =TRIM(MID(A2,FIND(",",A2+2),99))+0

    all you're doing is stripping out the preceding weekday name & comma -- then coercing the resulting string to a number c/o +0
    in my example I just used REPLACE -- so replace all chars between 1 and result of FIND with null, then TRIM and coerce.

    the issue with these calcs is locale - so what works in one location won't necessarily work in another - in the UK I could not use this formula as XL will not recognise the resulting string as a valid date/time construct.
    you can create more robust alternatives to work globally, but if you don't need to I wouldn't worry.

    more generally; in XL, as I am sure you know, Dates are just serial numbers / integers with Time being decimal (given 1 = 24 hours) -- we can mask those numbers to look like a Date/Time c/o Formats.

  5. #5
    Registered User
    Join Date
    09-20-2010
    Location
    islamabad
    MS-Off Ver
    Excel 2016
    Posts
    28

    Re: convert lenghty text to date

    Thanks Xlent for the time and explaining the cocept in detail much appricated

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,216

    Re: convert lenghty text to date

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
    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.

+ 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: 7
    Last Post: 07-11-2019, 11:27 AM
  2. Replies: 2
    Last Post: 05-15-2019, 03:24 PM
  3. [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
  4. [SOLVED] Convert a Text String Date to Date Serial Number
    By herve73 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-10-2015, 10:53 AM
  5. [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
  6. [SOLVED] Use VBA to run "text to columns' Loop on first row to convert from text to date format
    By csmiley in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 06-03-2013, 02:06 PM
  7. Range.Value2 is failing for lenghty strings
    By Naresh Mirkhelkar in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-09-2006, 10:00 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