Page 61 of 61 FirstFirst ... 1151596061
Results 601 to 603 of 603

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

  1. 04-17-2024, 06:52 PM #601
    DocAElstein
    DocAElstein is offline
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,313
    Rep Power
    10

    Some Date Stuff https://eileenslounge.com/viewtopic.php?f=30&t=40899

    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""))")
    Last edited by DocAElstein; 04-18-2024 at 02:55 AM.
    Reply With Quote Reply With Quote
  2. 04-17-2024, 11:13 PM #602
    DocAElstein
    DocAElstein is offline
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,313
    Rep Power
    10
    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
    Last edited by DocAElstein; 04-18-2024 at 01:35 AM.
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
     Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!
    Reply With Quote Reply With Quote
  3. 04-18-2024, 02:35 AM #603
    DocAElstein
    DocAElstein is offline
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,313
    Rep Power
    10
    Later
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Similar Threads

  1. Testing Concatenating with styles
    By DocAElstein in forum Test Area
    Replies: 2
    Last Post: 12-20-2020, 02:49 AM
  2. testing
    By Jewano in forum Test Area
    Replies: 7
    Last Post: 12-05-2020, 03:31 AM
  3. Just testing. Testing some sort routines. No reply needed
    By DocAElstein in forum Test Area
    Replies: 18
    Last Post: 03-17-2019, 06:10 PM
  4. Concatenating your Balls
    By DocAElstein in forum Excel Help
    Replies: 26
    Last Post: 10-13-2014, 02:07 PM
  5. Concatenating array into cells based on conditions
    By Lis7 in forum Excel Help
    Replies: 1
    Last Post: 12-04-2012, 08:56 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Forum Rules