On Mon, 8 Jun 2009 11:34:02 -0700, Vic wrote:
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