+ Reply to Thread
Results 1 to 9 of 9

Convert Text Values To Date and Time

  1. #1
    Forum Contributor
    Join Date
    06-16-2008
    Posts
    287

    Convert Text Values To Date and Time

    Hi, I am trying to work out a way where I can convert Text Values into a Date and Time Format through the use of a macro. Currently I have 3 files and in each files I have Date and Time Values that are showing as Text Values and rather than going through the Text to Columns Method or the Split (left, mid, right) Method, I was hoping somebody can advise any macro code that could do this for me quicker.

    The ranges each differ but on average I could have around 150k rows of data for 1 file and this ranges from 3/4 columns on each file.

    The following is an example of how the value is shown in the file.

    09-DEC-12 12.56.03.695 AM

    Currently I do find and replace to convert the . to : and than run the text to columns to remove the milliseconds this than converts the time to 24 hours and also gets rid of the AM/PM values but the problem is that this very time consuming.

    Any help or advice would be really appreciated.

    Thanks in advance.

  2. #2
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    Re: Convert Text Values To Date and Time

    How about:

    Please Login or Register  to view this content.
    and then format to suit.
    Gary's Student

  3. #3
    Forum Contributor
    Join Date
    06-16-2008
    Posts
    287

    Re: Convert Text Values To Date and Time

    Jakobshavn, thanks for your response. Would this work on all columns or would i need to select specific columns to run this? Also I have tried to run the macro and I get the following error:

    Run-time error '13':

    Type mismatch

    When I debug this it points to the dt = DateValue(Array(0)).

    Thanks btw for your help, really appreciate it.

  4. #4
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    Re: Convert Text Values To Date and Time

    It will work on cells that you Select.
    Try it on one or two cells first.

    It worked on your posted value on my computer.

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: Convert Text Values To Date and Time

    You could also do it with this formula:

    =DATE("20"&MID(A1,8,2),MATCH(MID(A1,4,3),{"JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC"},0),LEFT(A1,2)) + VALUE(SUBSTITUTE(MID(A1,11,8),".",":"))+IF(MID(A1,11,2)="12",-0.5,0)+IF(RIGHT(A1,2)="AM",0,0.5)

    assuming your date/time is in A1. Format the cell accordingly, then copy down.

    Hope this helps.

    Pete

  6. #6
    Valued Forum Contributor
    Join Date
    11-15-2008
    Location
    ph
    MS-Off Ver
    2007/2010/2016
    Posts
    479

    Re: Convert Text Values To Date and Time

    Hi -

    Select all affected cells and try;
    Please Login or Register  to view this content.
    Regards,
    Event

  7. #7
    Forum Contributor
    Join Date
    06-16-2008
    Posts
    287

    Re: Convert Text Values To Date and Time

    thanks guys for all your inputs, I will try them out and let you no how i get on.

  8. #8
    Forum Contributor
    Join Date
    06-16-2008
    Posts
    287

    Re: Convert Text Values To Date and Time

    event21, thanks that works exactly how i needed it. Just out of curiosity how can i show the time as hh:mm:ss (including the secs).

    Please Login or Register  to view this content.
    I added :ss but this made no difference.

    Thanks once again for your help.

  9. #9
    Valued Forum Contributor
    Join Date
    11-15-2008
    Location
    ph
    MS-Off Ver
    2007/2010/2016
    Posts
    479

    Re: Convert Text Values To Date and Time

    Hi -

    Try;
    Please Login or Register  to view this content.
    Regards,
    Event

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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