date format help

[Gerald]

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

[josnah]

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

[mrice]

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?

[Gerald]

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
>
>

[Dave Peterson]

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]

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
>

[Fred Smith]

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" <[email protected]> wrote in message
news:[email protected]...
> 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]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >>
>
>
>

[David Biddulph]

"Gerald" <[email protected]> wrote in message
news:[email protected]...

> "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