Transform date from "from Friday 4 to Sunday 6 January" to "2013-01-04" and "2013-01-06"

the "from Friday 4 to Sunday 6 January 2013" is all in 1 cell?

assuming the data is in A1, in B1 use this...
=DATEVALUE(MID(A1,SEARCH(" ",A1,SEARCH(" ",A1,1)+1)+1,2)&" "&MID(A1,SEARCH(" ",A1,SEARCH(" ",A1,SEARCH(" ",A1,SEARCH(" ",A1,SEARCH(" ",A1,1)+1)+1)+1)+1)+2,LEN(A1)-5))
and in C1 use this...
=DATEVALUE(MID(A1,SEARCH(" ",A1,SEARCH(" ",A1,SEARCH(" ",A1,SEARCH(" ",A1,SEARCH(" ",A1,1)+1)+1)+1)+1)+1,2)&" "&MID(A1,SEARCH(" ",A1,SEARCH(" ",A1,SEARCH(" ",A1,SEARCH(" ",A1,SEARCH(" ",A1,1)+1)+1)+1)+1)+2,LEN(A1)-5))

format custom YYYY-MM-DD

it worked find on my side. it may be a regional thing, in which case you will need to change all the , to ;

to test that, type in any function that needs a criteria, eg vlookup() , and see if it uses , or ; to separate things

If that still doesnt work, perhaps it would be easier to help you if you uploaded a sample workbook, showing what data you are working with, a few examples of what your expected outcome would be, and how you would arrive at that (remove any confidential info if necessary).

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.