+ Reply to Thread
Results 1 to 12 of 12

Convert date/time text to date/time values

  1. #1
    Registered User
    Join Date
    11-23-2020
    Location
    Greece
    MS-Off Ver
    16
    Posts
    10

    Question Convert date/time text to date/time values

    Hello, first of all I must say I am very new to Excel so please be comprehensive. I am importing some data from a pdf table with tabula and it works great so far but I have an issue. The data I am entering are names, dates, addresses etc. They show up fine but every time I need to double click or F2 each cell in order for it to be shown correctly. Example of what I mean is included in the screenshot. Basically on the A column is what I am copying and on B column is how its formatted after I double click or F2 and what I want. Is there any way to do it on the whole column?

    SharedScreenshot.jpg

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

    Re: How can I double click (F2) a whole column

    Welcome to the Forum Panos1221!

    Your data is text, not dates. F2 forces Excel to convert them. You can't literally double-click a whole column but you can do this:

    Put a 0 in any cell
    Select that cell then Copy
    Select all your dates
    Use Paste Special, and select Add

    It will force the conversion.

    If that doesn't work, attach your file so we can see what is going on. See yellow banner at the top.

  3. #3
    Registered User
    Join Date
    11-23-2020
    Location
    Greece
    MS-Off Ver
    16
    Posts
    10

    Re: Convert date/time text to date/time values

    Thanks for your answer 6StringJazzer!
    I think I upload the test sample. I am pretty sure you don't understand anything but at least you get the formatting. All the columns should be like the first column, I formatted using double clicks. I also tried your steps but they did nothing. Also, they are not only dates there are addresses and names which are getting formatted the same way with the dates (f2/double click)
    Attached Files Attached Files

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    19,901

    Re: Convert date/time text to date/time values

    If you are familiar with Power Query, you could bring your range/table into the PQ editor and highlight both columns and then change the data type to Date/Time. I could not because I use the US date format and not the EU format and it would add extra steps that would not be pertinent to your issue. Once changed you can then close and load the data back to Excel Native.

    Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

    It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

    - Follow this link to learn how to install Power Query in Excel 2010 / 2013.

    - Follow this link for an introduction to Power Query functionality.

  5. #5
    Registered User
    Join Date
    11-23-2020
    Location
    Greece
    MS-Off Ver
    16
    Posts
    10

    Re: Convert date/time text to date/time values

    Hello alan, I dont know Power Query but I will take a look at it. However as I said there arent only dates there, there are names and addresses as well.

  6. #6
    Forum Contributor
    Join Date
    08-26-2016
    Location
    UK
    MS-Off Ver
    O365
    Posts
    258

    Re: Convert date/time text to date/time values

    When I tried F2, followed by Enter on your data it looked correct; it put the time element on a new line within the cell and applied word wrap to the cell but it still didn't convert to dates/times - they remained as strings. In fact, deeper digging showed that all the F2 then Enter did was to convert the single carriage return in the cells to a line feed and apply word wrap.
    The danger with relying on Excel's interpretation of dates is that correct interpretation depends on your locale; do it in the US on your data and some of the dates will be wrongly interpreted (in the UK, since the date strings are in the format d/m/y all will be interpreted correctly).
    Had your date strings not contained a time element I'd have advised using Text-to-Columns, but this doesn't work well with an included time element.
    Since the strings are all d/m/y you can select all the cells containing what look like dates and run this macro:
    Please Login or Register  to view this content.
    Quote Originally Posted by alansidman View Post
    I could not because I use the US date format and not the EU format and it would add extra steps that would not be pertinent to your issue.
    Alan, the extra steps are easy and don't add any actual steps to the query: highlight both columns, right-click one of the headers and choose Change Type then choose Using locale… In the resultant dialogue box, choose Date/Time in the first field and English (United Kingdom) in the second. That's it.

  7. #7
    Registered User
    Join Date
    11-23-2020
    Location
    Greece
    MS-Off Ver
    16
    Posts
    10

    Re: Convert date/time text to date/time values

    Quote Originally Posted by p45cal View Post
    When I tried F2, followed by Enter on your data it looked correct; it put the time element on a new line within the cell and applied word wrap to the cell but it still didn't convert to dates/times - they remained as strings. In fact, deeper digging showed that all the F2 then Enter did was to convert the single carriage return in the cells to a line feed and apply word wrap.
    The danger with relying on Excel's interpretation of dates is that correct interpretation depends on your locale; do it in the US on your data and some of the dates will be wrongly interpreted (in the UK, since the date strings are in the format d/m/y all will be interpreted correctly).
    Had your date strings not contained a time element I'd have advised using Text-to-Columns, but this doesn't work well with an included time element.
    Since the strings are all d/m/y you can select all the cells containing what look like dates and run this macro:
    Please Login or Register  to view this content.
    Alan, the extra steps are easy and don't add any actual steps to the query: highlight both columns, right-click one of the headers and choose Change Type then choose Using locale… In the resultant dialogue box, choose Date/Time in the first field and English (United Kingdom) in the second. That's it.
    Hello p45cal, thanks for responding. I have one question when I make a button with the macro ( I am not familiar with macros and if there is an another way to run it like a quick snippet) I get the following error Expected End Sub and when I add End Sub to the end the code does nothing. I am sure I am not doing something correct myself thought

  8. #8
    Forum Contributor
    Join Date
    08-26-2016
    Location
    UK
    MS-Off Ver
    O365
    Posts
    258

    Re: Convert date/time text to date/time values

    You're right, I missed the End Sub statement at the end.
    The macro works on the selected cells, so make sure something is selected before you run the macro. If the selected cells all already have proper Excel dates in them they will be left alone.

  9. #9
    Registered User
    Join Date
    11-23-2020
    Location
    Greece
    MS-Off Ver
    16
    Posts
    10

    Re: Convert date/time text to date/time values

    Quote Originally Posted by p45cal View Post
    You're right, I missed the End Sub statement at the end.
    The macro works on the selected cells, so make sure something is selected before you run the macro. If the selected cells all already have proper Excel dates in them they will be left alone.
    Even with the correct script it does not work with the format i want like to put the time element on a new line within the cell. I am running out of options and I am thinking that maybe a macro that does f2 or double click a whole column would work best if possible

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

    Re: Convert date/time text to date/time values

    Quote Originally Posted by Panos1221 View Post
    I am pretty sure you don't understand anything
    I am going to chalk that up to a language barrier.

  11. #11
    Registered User
    Join Date
    11-23-2020
    Location
    Greece
    MS-Off Ver
    16
    Posts
    10

    Re: Convert date/time text to date/time values

    Quote Originally Posted by 6StringJazzer View Post
    I am going to chalk that up to a language barrier.
    Yes I mean for the data in the tables on the example :p

  12. #12
    Forum Contributor
    Join Date
    08-26-2016
    Location
    UK
    MS-Off Ver
    O365
    Posts
    258

    Re: Convert date/time text to date/time values

    Please Login or Register  to view this content.

+ 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: 02-06-2020, 10:04 AM
  2. [SOLVED] convert text date/time into a real date
    By sabha in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 09-23-2017, 02:34 PM
  3. Replies: 7
    Last Post: 06-19-2017, 06:10 PM
  4. [SOLVED] Convert Unix Date-time stamp to excel date and/or time columns
    By judikz in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-13-2015, 05:46 PM
  5. Convert Julian date/ time to regular date/ time (MM/DD/YYYY HH:MM:SS)
    By dataguy30 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-07-2013, 11:33 AM
  6. Convert Text Values To Date and Time
    By aftabn10 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-10-2012, 09:50 AM
  7. Replies: 0
    Last Post: 08-23-2005, 12:24 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