Excel Formula Help please.

[boxby]

I have downloaded a csv file from Protx, the date is in the format
29/03/2008 13:18 in column d2


I need to extract the date, in the dd/mm/yyyy format, and leave the time.
Into column e2

I have tried the Left formula
=LEFT(D2,10)

But it returns the answer in number format; 39535.5529

Despite the whole of columns d & e being formated into date, and not general or number, or anything else.

Please help. If I get this sussed I can upload the CSV file into the accounts rather than type the whole bloomin lot in manually!

Many thanks
Sandra

Despite me checking that the column

[Maxine]

Have you tried doing Text to columns?

[Peter W]

In cell e2, enter =INT(d2)
Then format the E column to your preferred format as normal.

Hope this works!

[admagic]

The problem is the original date is autoconverted into the underlying format

So you need to convert it back...

There may be an easier way but this works...

=DAY(D2)&"/"&MONTH(D2)&"/"&YEAR(D2)

similar for time

BTW

It is far easier and more versatile to do things like this using macro

[boxby]

I've done it! Memory from 10 years ago!!!

It's because the left function works on text, and the downloaded date column was a date column, so i need to put a ' before the date, then it reads it as text, and then it extracts the first 10 letters only!

Blimey, can't believe I remembered that (it's must have been the only useful thing that came out of that job!!)