would appreciate if I get help in my query

I have a excel file exported which has 500 rows in which date is in
mm/dd/yyyy format.

Need to have in dd/mm/yyyy format, tried text to columns, custom change date
format, nothing seems to work

plz help

thanks

Try This. You will have to select the range of cells first.

Sub Fixmmddyyyy()
If MsgBox("Fix mm/dd/yyyy to dd/mm/yyyy?", vbOKCancel) = vbCancel
Then Exit Sub
Application.ScreenUpdating = False
Dim Cell As Range
Selection.NumberFormat = "dd/mm/yyyy"
On Error Resume Next
For Each Cell In Selection
If Len(Cell) = 10 Then
Cell.Value = DateSerial(Right _
(Cell.Value, 4), Left(Cell.Value, _
2), Mid(Cell.Value, 4, 2))
End If
Next Cell
Application.ScreenUpdating = True
End Sub


--
josnah
------------------------------------------------------------------------
josnah's Profile: http://www.excelforum.com/member.php...fo&userid=6334
View this thread: http://www.excelforum.com/showthread...hreadid=548068

If you try to change the format of the date cell to general, what
happens? Do you get a five digit number beginning with a 3?

If you don't then Excel is not recognising the value as a date. Do you
have any leading or trailing spaces on the date - these might cause
problems?


--
mrice

Research Scientist with many years of spreadsheet development experience
------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=548068

yes, I am getting 5 digit number beg. with 3



"mrice" wrote:


If you try to change the format of the date cell to general, what
happens? Do you get a five digit number beginning with a 3?

If you don't then Excel is not recognising the value as a date. Do you
have any leading or trailing spaces on the date - these might cause
problems?


--
mrice

Research Scientist with many years of spreadsheet development experience
------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=548068

If you format the cell in an unambiguous date format (mmmm dd, yyyy), do your
dates still look like what you expect?

01/02/2006
could be January 02, 2006 or February 01, 2006.

If the dates are correct, just change the format to dd/mm/yyyy.

If the dates are not correct, I would re-import those values once more.

If they come from a text file, you can specify that your dates are already in
dmy order.

Gerald wrote:

would appreciate if I get help in my query

I have a excel file exported which has 500 rows in which date is in
mm/dd/yyyy format.

Need to have in dd/mm/yyyy format, tried text to columns, custom change date
format, nothing seems to work

plz help

thanks

--

Dave Peterson

did everything as suggested but still the date is in mm/dd/yyyy format

plz help

many thanks

"Dave Peterson" wrote:

If you format the cell in an unambiguous date format (mmmm dd, yyyy), do your
dates still look like what you expect?

01/02/2006
could be January 02, 2006 or February 01, 2006.

If the dates are correct, just change the format to dd/mm/yyyy.

If the dates are not correct, I would re-import those values once more.

If they come from a text file, you can specify that your dates are already in
dmy order.

Gerald wrote:

would appreciate if I get help in my query

I have a excel file exported which has 500 rows in which date is in
mm/dd/yyyy format.

Need to have in dd/mm/yyyy format, tried text to columns, custom change date
format, nothing seems to work

plz help

thanks

--

Dave Peterson

Now you know you have text, not an Excel date. You have two choices:

1. Keep the data as text, in which case you would convert to dd/mm/yyyy format
using:
=mid(a1,4,3)&left(a1,3)&right(a1,4)

2. Convert to an Excel date. Text to Columns should do this for you. If it
doesn't, it likely means you have extraneous characters in your cells. You can
convert the date yourself using something like:

=date(right(a1,4),left(a1,2),mid(a1,4,2))

but you may have to adjust the character positions based on the extraneous
characters you have.

--
Regards,
Fred


"Gerald" wrote in message
...
did everything as suggested but still the date is in mm/dd/yyyy format

plz help

many thanks

"Dave Peterson" wrote:

If you format the cell in an unambiguous date format (mmmm dd, yyyy), do your
dates still look like what you expect?

01/02/2006
could be January 02, 2006 or February 01, 2006.

If the dates are correct, just change the format to dd/mm/yyyy.

If the dates are not correct, I would re-import those values once more.

If they come from a text file, you can specify that your dates are already in
dmy order.

Gerald wrote:

would appreciate if I get help in my query

I have a excel file exported which has 500 rows in which date is in
mm/dd/yyyy format.

Need to have in dd/mm/yyyy format, tried text to columns, custom change
date
format, nothing seems to work

plz help

thanks

--

Dave Peterson

thanks for your reply

used the formulae =MID(A5,4,3)&LEFT(A5,4)&RIGHT(A5,1) to get the output as
dd/mm/yyyy

for the fields with single digit dates e.g 06/07/06 works fine and gives me
result as 07/06/06 but if the field has 06/10/06 it gives me an output as
6/06/106

please help

thanks



"Fred Smith" wrote:

Now you know you have text, not an Excel date. You have two choices:

1. Keep the data as text, in which case you would convert to dd/mm/yyyy format
using:
=mid(a1,4,3)&left(a1,3)&right(a1,4)

2. Convert to an Excel date. Text to Columns should do this for you. If it
doesn't, it likely means you have extraneous characters in your cells. You can
convert the date yourself using something like:

=date(right(a1,4),left(a1,2),mid(a1,4,2))

but you may have to adjust the character positions based on the extraneous
characters you have.

--
Regards,
Fred


"Gerald" wrote in message
...
did everything as suggested but still the date is in mm/dd/yyyy format

plz help

many thanks

"Dave Peterson" wrote:

If you format the cell in an unambiguous date format (mmmm dd, yyyy), do your
dates still look like what you expect?

01/02/2006
could be January 02, 2006 or February 01, 2006.

If the dates are correct, just change the format to dd/mm/yyyy.

If the dates are not correct, I would re-import those values once more.

If they come from a text file, you can specify that your dates are already in
dmy order.

Gerald wrote:

would appreciate if I get help in my query

I have a excel file exported which has 500 rows in which date is in
mm/dd/yyyy format.

Need to have in dd/mm/yyyy format, tried text to columns, custom change
date
format, nothing seems to work

plz help

thanks

--

Dave Peterson

"Gerald" wrote in message
...

"Fred Smith" wrote:

Now you know you have text, not an Excel date. You have two choices:

1. Keep the data as text, in which case you would convert to dd/mm/yyyy
format
using:
=mid(a1,4,3)&left(a1,3)&right(a1,4)
....

thanks for your reply

used the formulae =MID(A5,4,3)&LEFT(A5,4)&RIGHT(A5,1) to get the output as
dd/mm/yyyy

for the fields with single digit dates e.g 06/07/06 works fine and gives
me
result as 07/06/06 but if the field has 06/10/06 it gives me an output as
6/06/106

Wouldn't =MID(A5,4,3)&LEFT(A5,3)&RIGHT(A5,2) make more sense?
I can't see why you've taken 4 characters for the month and only one for the
year? That wasn't what Fred suggested.
--
David Biddulph