Convert Timestamp vaule to date

**dpatel** · 07-22-2010, 11:08 AM

Hi,

I have got a series of values like 21 Jul 2010 07:12PM which i would like to convert into date formate in Excel.
Exmaple:
21 Jul 2010 07:12PM
to
dd/mm/yyyy (21/07/2010)

Any help would be appriciate.

Thanks

**Domski** · 07-22-2010, 11:12 AM

Assuming those are text strings:

=INT(SUBSTITUTE(SUBSTITUTE(A1,"PM"," PM"),"AM"," AM")*1)

Dom

**dpatel** · 07-23-2010, 02:44 AM

Thanks Dom, this will work for me but how can we convert to read as a "Date format (dd/mm/yyyy)" using same formula rather than changing a format cell to date?

Dipesh

**oldchippy** · 07-23-2010, 02:54 AM

Hi,

How about

=TEXT(INT(SUBSTITUTE(SUBSTITUTE(A1,"PM"," PM"),"AM"," AM")*1),"dd/mm/yyyy")

**DonkeyOte** · 07-23-2010, 02:55 AM

In terms of the formula you could I think (based on your sample) you could also use:

=0+LEFT(A1,LEN(A1)-7)

Format cell as dd/mm/yyyy or if you want the above as literal string rather than a date

=TEXT(LEFT(A1,LEN(A1)-7),"dd/mm/yyyy")