Altering the Find/Replace Function 2

[Mizzness]

All.

Presently I recorded the Find/Replace function to look for any data that had "19" & to replce it with "20".
Upon further review of this data (in mm/dd/yyyy format)I would like to know if there is a way to code Find/Replace so that it only looks for the first two digits of the yyyy area ?
In short, if the data comes in as 1921 can I alter Find/Replace so that the result is 2021 ?
I cannot change the source. Sometimes the data comes through this way.

Recorded code:

Range("E6").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Replace What:="19", Replacement:="20", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False


Thanx for any & all help/guidance.

 

[SkipVought]

Hi,

Dates are NUMBERS like right now in North Texas the Date/Time VALUE is 39547.46132. Century 19 and 20 do not appear in the VALUE. They appear in the FORMAT, which is ONLY a display feature.

faq68-5827

If year(YourDate)<2000 then YourDate + 100

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[Mizzness]

Thanks Skip,

How could I also incorporate this if the last two digits are "19" ?
I.E.: 2019 should be 2020.

thanx again.

 

[SkipVought]

You should not be searching for ANY string. "19" or "20" are DISPLAY values, not DATE VALUES.

Do you understand what you are working with? Dates are NUMERIC values...

for each r in range([E6], [E6].end(xldown))
  If year(r.value)<2000 then r.value + 100
next

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[Bong]

I think an Excel date is the number of days since 1-jan-1900.

In any event, 1-jan-2000 is 36526. So that's the number you need to use for comparison. Then, if the date (that's an integer) is greater than 36526:
dateserial(year(x)+100,month(x),day(x)), where x is the date.

_________________
Bob Rashkin

 

[Bong]

I mean "less than"

_________________
Bob Rashkin

 

[zollo9999]

Hey Skip, Your tone sounds a bit mean an unnecessary.

Yes you are right that Microsoft products store dates as numeric values but that does not dictate there being only one way to skin the cat.

Mizzness. Have a look at this alternative.

Dim strDateLeft as string
Dim strDateRight as string
strDateLeft = Left$(cstr(Format(YourDate, "yyyy")),2)
strDateRight = Right$(cstr(Format(YourDate, "yyyy")),2)

You can then do a Find/Replace on each of strDateLeft and
strDateRight.

Note there are different Find/Replace methods depending on which product you are using Access, Excel or Word etc.



Zollo VBA Developer

 

[SkipVought]

I, obviously made a big mistake with the code I posted. Where was my head????

However, my point emphasizing the numeric solution is that a text solution implies and reinforces the misconception that a date is the text value that is seems. It is why I wrote faq68-5827.

Can the task in question be done with text? YES! Excel, does the Text <==> Date conversions for you, without you even realizing that it's happening. Making such assumptions can bite you from time to time. I'd rather AVOID such possibilities, however remote.

Sorry, again for my failing!

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[zollo9999]

No Problem
Understanding how Dates work is very valuable for anyone programming in VB/A, so good on you for making the effort to hightlight what dates are and how they can be manipulated and displayed.




Zollo VBA Developer