Date & time conversion

He Andy,

Just select the cells/columns with the dates and select "more number formats" in a dropdown list on the home tab (sorry if it's not called that way, I'm on excel 2013)
It probably displays as "date" in that dropdown list at the moment. Then select "custom" and on the right in the "type" box, type the format you want:
dd/mm/yyyy hh:mm


edit: I just downloaded your sample and I understand your issue a bit better now...
That's pretty weird... the format doesn't seem to apply to the bottom part with US formatting.

I'll be back in a bit and try to figure out what's wrong.

Cheers,

Michael.

Changing format wont work here, the cells in question are text, not numeric...all formats do is change how the data is displayed, not the underlying contents

Try this instead....

Highlight the entire range
select Data Tab/Text2Columns
click next...next...(doesnt matter what delimiter was selected)
in the 3rd box, check "DATE", and select your format
click FINISH

Perhaps in the raw data, there is an extra space or something somewhere

I have some data from a temperature logger

so who knows how that import could affect some data?

That T2C trick is very useful to remember - it wont affect real dates, and will often "fix" text dates
This would also have worked in a helper column, but is not needed in this case
=DATEVALUE(A1)

I followed the following procedure and got Excel recognizable dates and times.

Select the text dates. Go to Data, Text to Columns, choose Delimited, Next, choose Space. Next
Select the column with the date part and choose DATE and choose MDY as the format. Click on the AM/PM and do not import.
Click finish. In C15 enter =A15+B15. Copy the result and Paste Values and Number Formatting. Delete the data in columns B and C produced by this procedure.

There was an oddity in this procedure that I can't explain. When completing the Paste Values and Number formatting procedure, the Date in column B changed by adding one day to the result but the copy was correct

hi andy. try this in B2 & copy down:
=IFERROR(TEXT(TEXT(A2,"yyyyddmm"),"00-00-00")+MOD(A2,1),DATE(20&MID(A2,7,2),LEFT(A2,2),MID(A2,4,2))+MID(A2,FIND(" ",A2)+1,11))

Edit: if you see numbers, format cells to your desired format

@newdoverman:
can't seem to replicate your scenario.

Copy the result and Paste Values and Number Formatting. Delete the data in columns B and C produced by this procedure.

There was an oddity in this procedure that I can't explain. When completing the Paste Values and Number formatting procedure, the Date in column B changed by adding one day to the result but the copy was correct

i thought column B has the time & C has the formula? so i deleted A & B instead after copying & pasting values to C. i did get the correct results

@benishiryo.... My end results were good but the values that I copied for some reason changed to 1 day in advance after I copied them. I tried it several times and it did the same thing every time. If I hadn't seen it, I wouldn't have believed it...maybe too much wine for supper.

My straight-forward T2C worked fine, for the entire data range?

@newdoverman:
yeah, and i couldn't replicate it. you might be right about the drinks~

@Ford:
it doesn't if you change your Date Region settings to DMY (like me & probably both OP & newdoverman).

No, that is what I set it to (DMY)...thats what they needed, and they come from UK, so thats the format they would use

My settings as suspected are DMY. Sometimes this gets in the way if converting to or from MDY. In that case I change to the serial number and reformat as needed as the serial number is the basis of all date presentations.

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.