Transform Web Date to date

[sal21]

...
Dim valore as String
Dim DATA_AGG as Date
valore="Mon, 04 Mar 2024 08:48:08 GMT"
...

I need to transform the web date to DATA_AGG="04/03/2024"

[HansV]

Code: Select all

    Dim v() As String
    Dim d As Long, m As Long, y As Long
    v = Split(valore)
    d = v(1)
    m = (InStr("JanFebMarAprMayJunJulAugSepOctNovDec", v(2)) + 2) \ 3
    y = v(3)
    DATA_AGG = DateSerial(y, m, d)

[p45cal]

DATA_AGG = DateValue(Mid(valore, 6, 11))

[HansV]

I thought of that, but would it work in an Italian-language version of Windows?

[p45cal]

I don't know!

[HansV]

We may never know...

[DocAElstein]

We may never know...

Some things can really make you , I know, I feel your pain,
Never mind, .... just for fun, my take on it , just out of interest,
If I had to put money on it, ( and I wouldn’t Bet too much, as I find dates a bit tricky), but I would say, if I had to:…
I think Hans code will likely work for sal21, and I think Pascal’s probably will or won’t, but I am not 100% sure

Here’s my reasoning, …

So I tried it in a standard default setting German computer and German Excel.
Using Hans, I end up with
04.03.2024
, but probably sal21 won’t, cos ... Hans told me …..The standard date format in Italy is dd/mm/yyyy,…..


(To get Han’s to work for me, I would need to change the declaration of DATA_AGG to String, and add another line, like Let DATA_AGG = Format(DATA_AGG, "dd\/mm\/yyyy") )

Yours don’t work for me Pascal, , but it would, , if instead of Mar in his string, it had Mär, or Mrz ( and for me I would also need that extra line again )
I am thinking, maybe by the same reasoning, it probably will work for sal21, for the particular example, and for this month, ( and without the extra line ) as I am guessing that the three word equivalent of Italian Marzo might be Mar, and Apr for Aprile, maybe a couple of other months may work as well, .... but ...... Oh no! .... then there is these other months…. Gennaio, , , , maggio, giugno, luglio, agosto, settembre, ottobre, , dicembre ....





Alan

( P.S: The other little bit of useful info, which Hans told me, but I had already learnt the hard way…. the / is an awkward thing when playing around with dates, - it tells VBA to use the system date separator, a bit mean , ( at least to many of us, for sal21, the / probably tells VBA to use the /) )



( Edit: Oops, I has some things slanting the wrong way, - sorry, I live on a slope , I think I have corrected them correctly now )

[HansV]

/ is treated as the system date separator. \/ forces / to be used as a literal character regardless of system settings.

[snb]

Code: Select all

Sub M_snb()
  c00 = "Mon, 04 Mar 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),"mmm"))], 0) & "-"))
End Sub

Für unsere Deutsche Freunde:

Code: Select all

Sub M_snb()
  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

[HansV]

Sal21 uses VB6, not Excel (I have probably mentioned this before).

[sal21]

Sal21 uses VB6, not Excel (I have probably mentioned this before).

hI bro.
For the future a write in the title:
I'm on Vb 6.0

[DocAElstein]

Sal21 uses VB6, not Excel (I have probably mentioned this before).

…For the future a write in the title:
I'm on Vb 6.0

Hans has said it a few times, as I remember. Perhaps it’s debatable who when if anyone should say it, but just IMVHO, my 2 cents, that’s all. What I mean is, it most likely does no harm for it to be mentioned now and again by anyone: I expect in the meantime most of us know Sal21 uses VB6. But VB6 and VBA have a historical link and some similarities, at least as I understand it? Sometimes our digressions away in the VBA direction are interesting: both for their own sake; and/ but also they can give other ideas that may or may not then work for VB6.
Just my 2 cents, that’s all, :)

_.___________-
Anyways … ,

......

.. some feedback / comments on the 2 snb ( VBA ) offerings - I took as look
Some interesting ideas. The Excel TEXT( , ) thingy was the new thing for me and that is the new little gem of info which in conjunction with some pretty Evaluate techniques gets us something like this, in the language of your choice,
{"Jan", "Feb", "Mrz", "Apr", "Mai", "Jun", "Jul", "Aug", "Sep", "Okt", "Nov", "Dez"}

I expect in VBA the first snb offering might do something close to what Sal21 wants. It might give something similar to this
04/03/2024 08:48:08
, I am not so sure about the form the time bit might come out for Sal21, if he did try it in Excel VBA.
Its perhaps good to note again that in this particular requirement, the internal settings that Sal21 probably has, sometimes make things work by coincidence that otherwise might not work so well. That goes for snb offerings as well. In their current form they are not so generally useful, but never the less some good ideas, …. hidden in them, as often.

In VBA for standard German settings, for this sample text, Mon, 04 Mrz 2024 08:48:08 GMT , I get this for the second snb coding, ( so its assuming my sample text has a German 3 letter month abbreviation in it - the Mrz bit in the sample text )
04.03.2024 08:48:08
(I am not 100% sure if that would always work, possibly the day and month might get mixed up, but my initial guess is that in this case it might not get mixed up, until I try to get the format changed, then I expect there would be trouble, there usually is, Lol* )

Having looked at it, I think I can see what is going on.

Just for fun, and future reference, this would probably be a pretty VBA one line for me to get the 04/03/2024

Code: Select all

 Sub MakeItWorkLikeItShouldTLDR()  '   https://eileenslounge.com/viewtopic.php?p=316441#p316441
Dim Valory As String
 Let Valory = "Mon, 04 Mrz 2024 08:48:08 GMT"
Dim DaDte As String
 Let DaDte = Replace(Mid(Valory, 6, 11), Mid(Valory, 8, 6), "/" & Format(Application.Match(Mid(Valory, 9, 3), Evaluate("IF({1},TEXT(30*COLUMN(A:L),""[$-407]mmm""))"), 0), "00") & "/") ' 04/03/2024
End Sub




Sub MakeItWorkLikeItShould() ' https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=24136&viewfull=1#post24136
Dim Valory As String
 Let Valory = "Mon, 04 Mrz 2024 08:48:08 GMT"
Dim arrMtch() As Variant
 Let arrMtch() = Evaluate("IF({1},TEXT(30*COLUMN(A:L),""[$-407]mmm""))") ' {"Jan", "Feb", "Mrz", "Apr", "Mai", "Jun", "Jul", "Aug", "Sep", "Okt", "Nov", "Dez"}
Dim MtchRes As Long
 Let MtchRes = Application.Match(Mid(Valory, 9, 3), arrMtch(), 0)  ' 3
 Let MtchRes = Format(MtchRes, "00")  ' 03
Dim DaDte As String
 Let DaDte = Replace(Mid(Valory, 6, 11), Mid(Valory, 8, 6), "/" & MtchRes & "/") ' 04/03/2024
End Sub

It’s based to some extent on the snb coding, but changed a bit both
_ just to have it in a form I prefer, and
_ to make it work
One thing I noticed is that the CDate( ) can be a bit unkind if you are messing with formats, as can be declaring a variable as Date , which seems to do some similar conversion when applying something to it that looks like a date: The problem that can crop up is that it may convert the format back to some standard setting. In this requirement it probably would not be a problem for Sal21, just by lucky coincidence, because of his likely internal settings, but for most people, using it could be a bit of a time bomb waiting to mess you up.
(*It seems to me that you need to consider very carefully almost any use of dates in Excel , Excel VBA, and I expect VB6 as well, as there is likely to be a problem somewhere along the line.)


Probably to make that offering of mine work for Italian it would be similar, pretty well the same coding actually probably. The thing to change would be the [$-407]mmm thingy to match the language of the web string, just like that is the difference in the two snb codings. ( My guess is that if you leave out the [$-407] bit, then it might default to English?? I might prefer personally to explicitly do that somehow, - I mean tell it explicitly to be English, or whatever, if that is possible. I am not clued up about the Excel TEXT( , ) thingy, and I am not sure where best to look for stuff on that? Anyone any recommendations on that?


*I still think dates are usually tricky messy things in Excel, ( and probably VB6 as well) JIMHO.

Alan