Hello Again, hopefully this will be the last time I annoy anyone for a Solution.
The provided Data (attached) is where I am try to extract the Date and such is Driving me to the Wall.
Each Description is different and that's why formula or VBA has been working on some rows and not others.
Some Strings have full dates in them, others have just month and year and the rest have none or even week numbers.
Option Explicit
Function GetDate(ByVal cell As Range)
Dim m&, mon As String, s
mon = " JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC "
s = Split(cell)
For m = 1 To UBound(s)
If InStr(1, mon, s(m)) Then
If IsNumeric(Left(s(m + 1), 2)) Then
If Right(s(m - 1), 2) < 32 Then
GetDate = DateValue(Right(s(m - 1), 2) & " " & s(m) & " 20" & Left(s(m + 1), 2))
Else
GetDate = (InStr(1, mon, s(m)) + 2) / 4 & "/20" & Left(s(m + 1), 2)
End If
Else
GetDate = ""
End If
Exit Function
End If
Next
GetDate = ""
End Function
Bebo, Your code.....what can I say.....Just Brilliant, I just tested it on over 5000 rows. It did not miss a date. WoW its really Super) Cant thank you enough
Thank you for being Super wish I had asked earlier in time (So much frustration)
Hi Croweater, Thank you for your help in providing a solution.
I tested it side by Side with Bebo's code they both work super fast and accurate. However your code misses on a few Rows. Not sure why.
I am reattaching the sample with your code and Bebo Side by side so you can see where it misses.
Hi Bebo,
Do you think you could modify your code in the to retrieve just the year from the string rather than full date and/or month and date as your code is currently?
it seems that your code results recognizes the two year digit number as a proper year format rather than Excel formulas recognizing #23 as "1905" or "1900".
Having a real hard time with excel and dates
Hi Croweater,
Thank you for the reply.
I as well didn't realize that Month and Year would make it harder on me when "day" is not available in the string.
I discovered it after running the codes. Yours works real well too I use both codes on different columns
Cheers
OP
Hi Bebo,
Do you think you could modify your code in the to retrieve just the year from the string rather than full date and/or month and date as your code is currently?
OP
How many digits (2 or 4) or years do you expect?
In below UDF, 1 is for 2 digits, 2 for 4 digits
=getdate(D5,1)=23
=getdate(D5,2)=2023
If it not your expectation, try to manual input the outcome. (a few is enough)
PHP Code:
Option Explicit
Function GetDate(ByVal cell As Range, y As Integer)
Dim m&, mon As String, s
mon = " JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC "
s = Split(cell)
For m = 1 To UBound(s)
If InStr(1, mon, s(m)) Then
If IsNumeric(Left(s(m + 1), 2)) Then
If Right(s(m - 1), 2) < 32 Then
GetDate = DateValue(Right(s(m - 1), 2) & "/" & s(m) & "/20" & Left(s(m + 1), 2))
Else
GetDate = (InStr(1, mon, s(m)) + 2) / 4 & "/20" & Left(s(m + 1), 2)
End If
GetDate = Format(GetDate, IIf(y = 1, "yy", IIf(y = 2, "yyyy", "")))
Else
GetDate = ""
End If
Exit Function
End If
Next
GetDate = ""
End Function
Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
MS-Off Ver
MS 365 Subscription Insider Beta Channel v. 2507 (Windows 11 Home 24H2 64-bit)
Posts
91,786
Re: Extract Date from String
Originally Posted by Croweater
The post I was replying to and quoting was AT LEAST 4 posts previous!
You don't need to quote at all unless you need to draw attention to something specific in the post you are quoting or your post would not make sense without it. In this case, there was no ambiguity - your post makes complete and perfect sense without the quote, so no need for the clutter.
Ali Enthusiastic self-taught user of MS Excel who's always learning! Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy. You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests. Forum Rules (updated August 2023): please read them here.
LinkBack URL
About LinkBacks
Extract Date from String
Register To Reply
Originally Posted by onp
Bookmarks