Converting date from 01.01.2019 to 01/01/2019 (So excel recognises it!)

[JamesBowdidge]

Hi,

I have a database query that extracts a date in the following format 01.01.2019

I can convert it to slashes using substitute but excel does not recognise the formula result as a real date..

How can I force excel to recognise the date?

Obvious answer is to change the DB formatting but this isnt possible so I am stuck with this problem..

thanks

J

 

[Scott T]

=substitute(a16,".","/")+0

 

[Eric W]

Try the DATEVALUE function:

=DATEVALUE(SUBSTITUTE(A1,".","/"))

 

[njimack]

--substitute(a1,".","/")

 

[JamesBowdidge]

Both work like a charm! thanks..

 

[shg]

Instead of a formula, you could select the column and replace . with /