| i have tried with "text to columns" options converting this into correct date format still it is not working|
If the Text To Columns is not working
You can use the =DATEVALUE() function to convert the TEXT to DATE
But this will only work on Dates that are formatted in your Operating System Default Format.
In other words,
if you use the American style MM/DD/YYYY as your System Date,
then dates formatted in the European style of DD/MM/YYYY will not be changed correctly
and if you use the European Style the American style will not be converted correctly.
So what is your System Date formatted as?
I'm going to hazard a guess that you are using the European style date of DD/MM/YYYY and need to convert the American style.
For this we can use the =DATE() function where we first
split the date apart and then put it back together in the correct order.
With your American style Date in A1,
put this formula in B1:
This is for a Date with a 4 Digit Year.
See how that works.
message edited by mmcconaghy