+ 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 2019
    Posts
    83

    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
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    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 2019
    Posts
    83

    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
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    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 2019
    Posts
    83

    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
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,333

    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!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

+ 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. [SOLVED] 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