Converting date format?

[tQk.Sushi]

Hi all,

I have a question about converting dates. Is it possible that when I copy and paste a date onto excel it will convert itself into a different format?

Like for example if I paste the date "Sep 4th 11, 1:14 PM" into Excel that it will convert it into 9/4/11. The reason I'm asking is because the aforementioned date format is not recognized by Excel.

Much thanks

 

[cstimart]

Hi all,

I have a question about converting dates. Is it possible that when I copy and paste a date onto excel it will convert itself into a different format?

Like for example if I paste the date "Sep 4th 11, 1:14 PM" into Excel that it will convert it into 9/4/11. The reason I'm asking is because the aforementioned date format is not recognized by Excel.

Much thanks

Set up the entire column with the date format you want, then use right-click and "pastespecial" and select "value" when putting the date in the new column.

 

[Rick Rothstein]

You could put this formula in an unused cell on the same row and copy it down as needed...

=--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT(A1,FIND(",",A1)-1),"th",","),"st",","),"nd",","),"rd",",")
You could then Copy the cells (with the above formula) and use Paste Special, Values to replace your original "dates" with the real dates the formula generates, then use Format Cells to make the real dates look like you want.

 

[T. Valko]

You could put this formula in an unused cell on the same row and copy it down as needed...

=--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT(A1,FIND(",",A1)-1),"th",","),"st",","),"nd",","),"rd",",")
You could then Copy the cells (with the above formula) and use Paste Special, Values to replace your original "dates" with the real dates the formula generates, then use Format Cells to make the real dates look like you want.

Not extensively tested but it seems to work and retains the time portion.

=--SUBSTITUTE(SUBSTITUTE(A1,",",""),MID(A1,LOOKUP(50,FIND({"st","nd","rd","th"},A1)),2),",")

I formatted the cells as m/d/yyyy h:mm am/pm.

 

[Rick Rothstein]

Not extensively tested but it seems to work and retains the time portion.

=--SUBSTITUTE(SUBSTITUTE(A1,",",""),MID(A1,LOOKUP(50,FIND({"st","nd","rd","th"},A1)),2),",")

Just to point out... I deliberately removed the time portion because of this statement by the OP...

"...if I paste the date "Sep 4th 11, 1:14 PM" into Excel that it will convert it into 9/4/11."

 

[T. Valko]

Just to point out... I deliberately removed the time portion because of this statement by the OP...

"...if I paste the date "Sep 4th 11, 1:14 PM" into Excel that it will convert it into 9/4/11."

If they only want the date...

=--INT(SUBSTITUTE(SUBSTITUTE(A1,",",""),MID(A1,LOOKUP(50,FIND({"st","nd","rd","th"},A1)),2),","))

This one is a bit shorter:

=--REPLACE(LEFT(A1,FIND(",",A1)-1),LEN(LEFT(A1,FIND(",",A1)-1))-4,2,",")