change DD/MM/YY to MM/DD/YY

[Vic]

I need to change DD/MM/YY to MM/DD/YY in a description field but not in all
cells. The date is in the middle of description. All American description
fields have dates in MM/DD/YY and all European description fields have
DD/MM/YY. Please keep in mind that this is NOT a date field and the field
contains other words besides the date. The date is in the middle of the
description.
How do I do that?

 

[Otto Moehrbach]

Vic
Can you provide any logic with which to find the date in the cell entry?
Something perhaps like:
The date always starts with the 7th character in the cell.
Or
The first character in the date is always the first numerical character in
the cell.
Or
The date is always 8 characters long and is the only part of the entry that
has slashes.
Or
???
HTH Otto

 

[Vic]

Hi Otto,
I can select European rows and apply the change to them only. There are 2
possible descriptions in European rows:
1. Quest has it requisition #7847. Sample collected on 6/17/08
2. Not listed on Quest report. Sample collected on 4/23/08
I only need to change Europeans to look like Americans or all Americans to
look like European dates. I can't have any mix. The preference will be teh
European date format. I would like to change the above dates to look like
this: 17/06/08 and 23/04/08 with zero added to a day and a month.
Thank you.

 

[Otto Moehrbach]

Vic
Your response doesn't address any of the questions I posted. Am I
missing something? Otto

 

[Vic]

Hi Otto,
The date is always at the end of the field, it has 2 slashes and in format
MM/DD/YY. Leading zero is always truncated in MM and DD.
Thank you.

 

[Gary''s Student]

In cells A1 thru D1 enter:

I will meet you on 12/25/2009

=RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1,"
","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

=LEN(B1)

=LEFT(A1,LEN(A1)-C1) & TEXT(DATEVALUE(B1),"dd/mm/yyy")

We see in A1 thru D1:

I will meet you on 12/25/2009
12/25/2009
10
I will meet you on 25/12/2009

So the date at the end of the phrase in A1 has been converted to European
format in D1.

 

[Ron Rosenfeld]

Hi Otto,
I can select European rows and apply the change to them only. There are 2
possible descriptions in European rows:
1. Quest has it requisition #7847. Sample collected on 6/17/08
2. Not listed on Quest report. Sample collected on 4/23/08
I only need to change Europeans to look like Americans or all Americans to
look like European dates. I can't have any mix. The preference will be teh
European date format. I would like to change the above dates to look like
this: 17/06/08 and 23/04/08 with zero added to a day and a month.
Thank you.

I'm not sure if you want to make all look like Europeans, or all look like
Americans.

The following macro will reverse the dd/mm to mm/dd (or vice versa) on the
rows you select. It checks to make sure the "last word" contains to "/"'s, but
doesn't do any other error checking. So you can use this to switch those
positions; the result (all American or all European) will depend on what you
select.

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), first select the entries you wish to process. Then
<alt-F8> opens the macro dialog box. Select the macro by name, and <RUN>.

When you are satisfied that it works correctly, you can remove the Offset(0,1)
argument (see the documentation in the Macro, and your results will replace the
original.

======================
Option Explicit
Sub RevDayMonth()
Dim c As Range
Dim s
Dim sFirstPart As String
Dim sDate As String
Dim sTemp As String
For Each c In Selection 'assumes you select only entries to be processed
With c
'remove leading/trailing spaces for ease of processing
sTemp = Trim(.Value)
sFirstPart = Left(sTemp, InStrRev(sTemp, " "))
sDate = Right(sTemp, Len(sTemp) - InStrRev(sTemp, " "))
s = Split(sDate, "/")
'make sure that there are two /'s in string as check
If UBound(s) = 2 Then
sTemp = Right("0" & s(0), 2)
s(0) = Right("0" & s(1), 2)
s(1) = sTemp
sDate = Join(s, "/")
End If
'when debugged, remove the .Offset(0,1)
.Offset(0, 1).Value = sFirstPart & sDate
End With
Next c
End Sub
===================================
--ron