Converting from one date format to another.

**Tfq** · 02-01-2013, 10:14 AM

Hi, I have a date in excel which is in (yy/mm/dd hh:mm:ss) format,. It is fetched from and online tool. Now when I paste it in excel it recognizes it as (dd/mm/yy hh:mm:ss) that is (13/02/01 is taken as 13-DD 02-MM and 2001 as YYYY). Even though I format the cell to yy-mm-dd still excel takes 01 as yr and 13 as day.

Please help me fix this.
How do I tell that the date entered is in particular format and not to assume by itself.

I am using excel 2007

**Jakobshavn** · 02-01-2013, 10:59 AM

If what you see in the cell (say A1) after importing the material is:

13/02/01 09:34:16

and this should mean:

1 February 2013

then in another cell enter:

=DATE(2000+LEFT(A1,2),MID(A1,4,2),MID(A1,7,2)) and format the cell with the formula in any fashion you require.

**Tfq** · 02-02-2013, 11:54 AM

Hi Jakob,

Thanks for your reply,

I get "9/12/2038" when I put the formula. Also after entering the value when I double click it becomes "13/2/2001 9:34:16 AM". I have used excel moderately, But I always tend to have difficulty with date and time formatting.

Grateful if you can suggest on this. Plus do let me know any link where I can get more details on my issue.

Thanks in advance.