Convert Month Day, Year to dd/mm/yyyy

[Apple Ling]

Hi,

I have a list of date in Month Day, Year format and need to convert to dd/mm/yyyy. For example, May 2, 2013 become 02/05/2013.

I have tried DateValue and TEXT(A1,"DD-MMM-YYYY"). However, both formula do not work for me.

I hope someone may help.

Thanks,
Ling

[wenqq3]

Did try change the cell format to dd/mm/yyy ?

[FDibbins]

Try applying 1 of the standard date formats, or create your own under custom?

edit: wenqq beat me to it

[acerrhod]

if the date is a text string try this:
=TEXT(MID(A1,FIND(" ",A1)+1,SEARCH(",",A1)-(FIND(" ",A1)+1))&"/"&LOOKUP(LEFT(A1,FIND(" ",A1)-1),{"April","August","December","February","January","July","June","March","May","November","October","September"},{4,8,12,2,1,7,6,3,5,11,10,9})&"/"&RIGHT(A1,4),"DD/MM/YYYY")

[Apple Ling]

Thanks acerrhod. Your formula work well for me but there is an error when the date is 2 digits for example, April 30, 2013. The result become 03/04/2013.

Can you please help me to fix the problem. Thanks again.

[FDibbins]

what exactly are your dates - text or value? test with =isnumber() FALSE = text

[wenqq3]

Or you can upload a sample workbook, so we can work on it. Thanks

[FDibbins]

lol yup, what wenqq said

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[acerrhod]

OK TRY THIS
=TEXT(MID(A13,FIND(" ",A13)+1,SEARCH(",",A13)-(FIND(" ",A13)+1))&"/"&LOOKUP(LEFT(A13,FIND(" ",A13)-1),{"April","August","December","February","January","July","June","March","May","November","October","September"},{4,8,12,2,1,7,6,3,5,11,10,9})&"/"&RIGHT(A13,4),"DD/MM/YYYY")

check the attachment
thanks

[Kevin UK]

Hi

Assuming in A1 is the date as per your post #1 "May 2, 2013". Enter the following formula in say B1 and format the cells as "dd/mm/yyy" (Without the quotes)

Please Login or Register  to view this content.