Convert custom Date into Date format

**notforgoogle** · 11-06-2011, 06:46 AM

Hi all

I have dates in the following format: 22.5.08

I am trying to convert these into the dd/m/yy format but when I try and format the cells using a custom format (right click > Format Cells > Date) it does not convert the 22.5.08 into the desired format.

I have also tried using the dateformat function.

Anu suggestions would be appreciated.

**royUK** · 11-06-2011, 06:49 AM

They are probably imported dates & are text not numerical

**Richard Buttrey** · 11-06-2011, 07:09 AM

..and following Roy's post you can check whether they are numbers with the =ISNUMBER(A1) function which should return true if the cell ref is a number.

**notforgoogle** · 11-06-2011, 07:20 AM

Thanks for the response - in that case the only solution would be to remove the "." from the value and then apply the date format to the values?

I hope that make sense? If is there an easy way of removing the "."?

Thanks again

**Domski** · 11-06-2011, 07:23 AM

Just do a find...replace changing . to / and they should convert to serial dates.

Dom

**daddylonglegs** · 11-06-2011, 07:37 AM

Try using "Text to columns"

Select column of dates

Data > text to columns > Next > Next > under "column data format" select "Date" and "DMY" > Finish

**notforgoogle** · 11-06-2011, 08:22 AM

Thanks for all the suggestions. The easy fix was the text to columns approach and the other option was to do the following as suggested by others:

Use a substitute function on the data e.g. =substitute(22.01.2011,".","/") and then to do a dateValue on the result.

Thanks again