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

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.

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)

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.

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.

Originally Posted by alansidman

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.

Originally Posted by p45cal

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

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.

Originally Posted by p45cal

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

Originally Posted by Panos1221

I am pretty sure you don't understand anything

I am going to chalk that up to a language barrier.

Originally Posted by 6StringJazzer

I am going to chalk that up to a language barrier.

Yes I mean for the data in the tables on the example :p

Please Login or Register  to view this content.