Excel 2007 : Replace date range

[Matabele]

Hi everyone,

Second post today and had some excellent help earlier. I hope someone can help me with this problem. I am trying to replace a date and time range with just the corresponding date range. Please see the attachment. So I need to replace column "A" and have it look like column "C". I manually typed in the date range in Column "C" but the data set Im working with is so big this is not feasible for the whole set. Any ideas?

Thanks!

Help1.xlsx

[protonLeah]

B1: "=DATEVALUE(SUBSTITUTE(A1,".","/")))", copied down and formatted as date.

[Paul]

Hi Matabele,

You might try:

=DATEVALUE(MID(A1,6,2)&"/"&MID(A1,9,2)&"/"&LEFT(A1,4))

Format that cell as Date and then fill down the column. Hope that helps!

[Matabele]

Thanks very much for your help proton and Paul, it worked perfectly!!