Converting Dates from Text to Date Format

[talan]

Hi team,

I have been sent a spreadsheet from an external source.

The date is supplied to me in text format i.e. 2012-08-14 08:29:33.207

I would like to convert this format to simply dd/mm/yyyy
How do I go about doing this?

TIA!
Talan

[Tony Valko]

If you just want the date portion then you'll have to extract it to a new cell.

This worked for me...

A2 = 2012-08-14 08:29:33.207 (as a text string)

This formula in B2:

=--LEFT(A2,10)

Format in the date style of your choice.

[TMS]

If, for example, the value is in cell A1, try: =INT(A1) and format as a date. Drag down as required.

Regards, TMS

[Bil4646l]

I am new to this, but can't you go to the Numbers Formats on the home tab, and from drop down menu select "more Numbers Formats", then "Custom", and select a format you want?

[TMS]

@bil4646l: if it is text, then no. Changing the format of the cell to Date won't make a difference. Hence the reason for forcing Excel to convert the cell to a number.

Regards, TMS