convert date in excel

[phdwsm]

I am trying to complie a number of spreadsheets containing dates of appointments, unfortunately one of the spreadsheets the person has enter the date as 3rd Dec 2016 instead of 03/12/16.

How can i convert it please.

thank you in advance.

 

[gruntingmonkey]

I think the below link should tell you how to change the date to exactly what you want.

https://support.office.com/en-gb/ar...you-want-8e10019e-d5d8-47a1-ba95-db95123d273e

Let me know how you get on.

 

[phdwsm]

Hi i tried this but it would not change the format.

 

[gruntingmonkey]

Can you not delete what they have entered?

 

[phdwsm]

Hi

I hightlighted the column then used replace to remove the "st,nd,th" then used your suggestion to format cell to a custom date and it worked. thank you.

 

[admiral100]

Hi,

Try this :

=DATE(RIGHT(A1,4),MONTH(DATEVALUE(LEFT(TRIM(MID(A1,LEFT(FIND(" ",A1)),LEN(A1))),3)&" 1")),IF(ISNUMBER(VALUE(LEFT(A1,2))),LEFT(A1,2),LEFT(A1,1)))

 

[Dave Patton]

=SUBSTITUTE(A1,"rd ","")+0