Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)

**DocAElstein** · 04-17-2024, 06:52 PM

This is post https://www.excelfox.com/forum/showt...ge61#post24134
https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)/page61#post24134
https://www.excelfox.com/forum/showt...ll=1#post24134
https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=24134&viewfull=1#post24134

Some notes in support of this main forum post
https://eileenslounge.com/viewtopic....316419#p316419

Good Lord, WTF is this… a punishment, I think

Code:

 Sub M_snb() '     https://eileenslounge.com/viewtopic.php?p=316419#p316419
Dim c00 As String
  c00 = "Mon, 04 Mrz 2024 08:48:08 GMT"
  MsgBox CDate(Replace(Mid(c00, 6, 20), Mid(c00, 8, 5), "-" & Application.Match(Mid(c00, 9, 3), [transpose(text(30*row(1:12),"[$-407]mmm"))], 0) & "-"))
End Sub

Let’s first break it down / open it up a little bit

Code:

 CDate(Replace(Mid(c00, 6, 20), Mid(c00, 8, 5), "-" & Application.Match(Mid(c00, 9, 3), [transpose(text(30*row(1:12),"[$-407]mmm"))], 0) & "-"))
CDate(

Replace(Mid(c00, 6, 20), Mid(c00, 8, 5), "-" & Application.Match(Mid(c00, 9, 3), [transpose(text(30*row(1:12),"[$-407]mmm"))], 0) & "-")
)

Replace(
Mid(c00, 6, 20), Mid(c00, 8, 5), "-" & Application.Match(Mid(c00, 9, 3), [transpose(text(30*row(1:12),"[$-407]mmm"))], 0) & "-"
Expression     , find          , replace                                                                                                                , start,  count,  compare
)

Replace(expression, find, replace, start, count, compare ) https://learn.microsoft.com/en-us/of...place-function

So let’s look at this first Application.Match(Mid(c00, 9, 3), [transpose(text(30*row(1:12),"[$-407]mmm"))], 0)

The array, [transpose(text(30*row(1:12),"[$-407]mmm"))]
The final thing it ends up with is a 1 D array , ( as we need for the .Match ) , which looks like
{"Jan", "Feb", "Mrz", "Apr", "Mai", "Jun", "Jul", "Aug", "Sep", "Okt", "Nov", "Dez"}
The main thing there is the Excel TEXT function, which tries to return a text out of …._
_.... = TEXT( a number , the second argument is some recognised format )
For now I will have to believe that [$-407] means German dates and the mmm is likely what it always is in these date things: The month in 3 letters. The numbers generated are { 30, 60, 90 …. 360 }, so that is a nice way to get a day number for each month, so like we finally have, as example the second array element gives pseudo like
TEXT(60, [$-407]) = "Feb"

I might do the array a bit differently…

Code:

 Let vTemp = Evaluate("30*row(1:12)") '  {30; 60; 90; 120; 150; 180; 210; 240; 270; 300; 330; "360"}
 
 Let vTemp = Evaluate("text(30*row(1:12),""[$-407]mmm"")")  ' "Jan"
 Let vTemp = [text(30*row(1:12),"[$-407]mmm")]              ' "Jan"
 Let vTemp = Evaluate("if({1},text(30*row(1:12),""[$-407]mmm""))")  ' {"Jan"; "Feb"; "Mrz"; "Apr"; "Mai"; "Jun"; "Jul"; "Aug"; "Sep"; "Okt"; "Nov"; "Dez"}
 Let vTemp = [IF({1},text(30*row(1:12),"[$-407]mmm"))]              ' {"Jan"; "Feb"; "Mrz"; "Apr"; "Mai"; "Jun"; "Jul"; "Aug"; "Sep"; "Okt"; "Nov"; "Dez"}
 Let vTemp = [TEXT(30*COLUMN(A:L),"[$-407]mmm")]  ' "Jan"
 Let vTemp = Evaluate("TEXT(30*COLUMN(A:L),""[$-407]mmm"")")  ' "Jan"
 Let vTemp = [IF({1},TEXT(30*COLUMN(A:L),"[$-407]mmm"))]  ' {"Jan", "Feb", "Mrz", "Apr", "Mai", "Jun", "Jul", "Aug", "Sep", "Okt", "Nov", "Dez"}
 Let vTemp = Evaluate("IF({1},TEXT(30*COLUMN(A:L),""[$-407]mmm""))")  ' {"Jan", "Feb", "Mrz", "Apr", "Mai", "Jun", "Jul", "Aug", "Sep", "Okt", "Nov", "Dez"}
' So what we got is a 1 D array of the months as Excel has them for Germany

Evaluate("IF({1},TEXT(30*COLUMN(A:L),""[$-407]mmm""))")

**DocAElstein** · 04-17-2024, 11:13 PM

From last post ….

We got the 1 D array for the .Match thing,
Application.Match(Mid(c00, 9, 3), the 1 D array , 0)
Application.Match(Mid(c00, 9, 3), {" Jan", "Feb", "Mrz", "Apr", "Mai", "Jun", "Jul", "Aug", "Sep", "Okt", "Nov", "Dez" } , 0)
The Mid(c00, 9, 3) gets us the 3 letter month given in the original string date ( from the Web apparently )

So this gives us a position number which will also be the number of the date, starting at 1 for January.
For out data example, "Mon, 04 Mrz 2024 08:48:08 GMT", then Mid(c00, 9, 3) is Mrz , so we end up with 3

The Replace(
We do the Replace on just part of the original string , ( this is the original staring )Mon, 04 Mrz 2024 08:48:08 GMT
We do the Replace on just the Mid(c00, 6, 20) bit, which is 04 Mrz 2024 08:48:08
We replace the Mid(c00, 8, 5) bit, and that bit is _ Mrz _ ( five characters there – a trailing and leading space on the Mrz )
We end up with
04-3-2024 08:48:08
For the OP something else would have done, but that looks nice, it looks like a nice time date type form that things Excel/ VBA might recognise. So I am happy with that

At this stage, I am guessing that that will be recognised by Excel/ VBA as a date time thing. Sure enough it is, ( and in this case it does not seem to try and do anything to swap the day and month number around)
I end up with
04-03-2024 08:48:08
So it has added a leading 0 before the 3, but as said (on my Vista machine with settings of mm-dd-yyyy), it did not try to swap the 03 and 04 around

A few other remarks
Rem '2b
We probably do not need the CDate, - if a variable is declared as Date, then assigning the RHS to it will effectively do a similar convert to date.

Rem '2c
For this sort of thing, a / and a – seem to have a similar effect

Here is the coding so far

Code:

Sub Mrz_snb()  '  https://eileenslounge.com/viewtopic.php?p=316419#p316419
Rem 0 Somne stuff to help show the sort of date format is in the system
Dim XlToday As Date, VBAToday As Date
 Let XlToday = Evaluate("=Today()"): Let VBAToday = Date
Debug.Print Application.OperatingSystem & "   XLToday " & XlToday; "     VBAToday " & VBAToday



Dim Valory As String, vTemp As Variant, c00 As Variant
 Let Valory = "Mon, 04 Mrz 2024 08:48:08 GMT": c00 = "Mon, 04 Mrz 2024 08:48:08 GMT"
 
Rem  https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)/page61#post24134
 Let vTemp = Evaluate("30*row(1:12)") '  {30; 60; 90; 120; 150; 180; 210; 240; 270; 300; 330; "360"}
 
 Let vTemp = Evaluate("text(30*row(1:12),""[$-407]mmm"")")  ' "Jan"
 Let vTemp = [text(30*row(1:12),"[$-407]mmm")]              ' "Jan"
 Let vTemp = Evaluate("if({1},text(30*row(1:12),""[$-407]mmm""))")  ' {"Jan"; "Feb"; "Mrz"; "Apr"; "Mai"; "Jun"; "Jul"; "Aug"; "Sep"; "Okt"; "Nov"; "Dez"}
 Let vTemp = [IF({1},text(30*row(1:12),"[$-407]mmm"))]              ' {"Jan"; "Feb"; "Mrz"; "Apr"; "Mai"; "Jun"; "Jul"; "Aug"; "Sep"; "Okt"; "Nov"; "Dez"}
 Let vTemp = [TEXT(30*COLUMN(A:L),"[$-407]mmm")]  ' "Jan"
 Let vTemp = Evaluate("TEXT(30*COLUMN(A:L),""[$-407]mmm"")")  ' "Jan"
 Let vTemp = [IF({1},TEXT(30*COLUMN(A:L),"[$-407]mmm"))]  ' {"Jan", "Feb", "Mrz", "Apr", "Mai", "Jun", "Jul", "Aug", "Sep", "Okt", "Nov", "Dez"}
 Let vTemp = Evaluate("IF({1},TEXT(30*COLUMN(A:L),""[$-407]mmm""))")  ' {"Jan", "Feb", "Mrz", "Apr", "Mai", "Jun", "Jul", "Aug", "Sep", "Okt", "Nov", "Dez"}
' So what we got is a 1 D array of the months as Excel has them for Germany
 
 Let vTemp = Application.Match(Mid(c00, 9, 3), vTemp, 0) ' 3
 Let vTemp = Replace(Mid(c00, 6, 20), Mid(c00, 8, 5), "-" & vTemp & "-") ' 04-3-2024 08:48:08

Rem  https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=24135&viewfull=1#post24135
 Debug.Print CDate(vTemp) '  KB 04-03-2024 08:48:08  SSD2 04.03.2024 08:48:08
 Let vTemp = CDate(vTemp)
 Debug.Print vTemp        '  KB 04-03-2024 08:48:08  SSD2 04.03.2024 08:48:08
' 2b
Dim Dte As Date
 Let Dte = vTemp: Debug.Print Dte
Debug.Print CDate(Replace(Mid(c00, 6, 20), Mid(c00, 8, 5), "-" & Application.Match(Mid(c00, 9, 3), [transpose(text(30*row(1:12),"[$-407]mmm"))], 0) & "-"))

' 2c
 Let vTemp = Evaluate("IF({1},TEXT(30*COLUMN(A:L),""[$-407]mmm""))")  ' {"Jan", "Feb", "Mrz", "Apr", "Mai", "Jun", "Jul", "Aug", "Sep", "Okt", "Nov", "Dez"}
 Let vTemp = Application.Match(Mid(c00, 9, 3), vTemp, 0) ' 3
 Let vTemp = Replace(Mid(c00, 6, 20), Mid(c00, 8, 5), "/" & vTemp & "/") ' 04/3/2024 08:48:08

 Debug.Print CDate(vTemp) '  KB 04-03-2024 08:48:08  SSD2 04.03.2024 08:48:08
'Dte As Date
 Let Dte = vTemp: Debug.Print Dte '  KB 04-03-2024 08:48:08  SSD2 04.03.2024 08:48:08




End Sub
'
'    KB Vista
'    Windows (32-bit) NT 6.00   XLToday 04-17-2024     VBAToday 04-17-2024
'    04-03-2024 08:48:08
'    04-03-2024 08:48:08
'    04-03-2024 08:48:08
'    04-03-2024 08:48:08
'    04-03-2024 08:48:08
'    04-03-2024 08:48:08

'    SerSzuD2
'    Windows (32-bit) NT :.00   XLToday 17.04.2024     VBAToday 17.04.2024
'    04.03.2024 08:48:08
'    04.03.2024 08:48:08
'    04.03.2024 08:48:08
'    04.03.2024 08:48:08
'    04.03.2024 08:48:08
'    04.03.2024 08:48:08