Reversing the decimal separator auto date recognition function?

[Jaymond Flurrie]

Does someone here have a VBA script that reverses the date transforms? That is, when Excel sees stuff like 11.84, it says that "yippee, this is a date and I couldn't care less whether you think otherwise -> Nov.84 that is and you as a user can shove your ideas of numbers to where the sun doesn't shine". And no, changing that to number format definitely does not give anything close to correct results, since the number is counted from 1900 or 1904 dates and gives tens of thousands as the result.

So, I'm looking for a script that simply will undo the the date recognize. And no, this can't be any manual work of cell formattings etc. since I have accounting data of thousands of rows that comes from a source file, that might already be "infected".

Basically the function could be something like

Function reverseidiotictranslation(infecteddata As String)        
    Dim result As Double
    result = whateverthetranslationfunctionactuallyis(infecteddata)
    reverseidiotictranslation = result
End Function

but I think this has been already solved somewhere carefully, so most likely no need to re-invent the wheel.

 

[mole999]

can you save as csv, then import that setting your fields

 

[Jaymond Flurrie]

can you save as csv, then import that setting your fields

CSV looks like this:
-0.16;heinä.84;8
-0.10;kesä.90;3
-0.10;heinä.40;4

(heinä = jul., kesä = jun.)
I'm almost 100% sure it is possible to count from there what the numbers are. I _think_ those would be 7.84, 6.90 and 7.40, but someone who knows the Excel system better could confirm that.

 

[mole999]

so a find replace of heinä. to 'jul., thats 12 events over the whole file ? and adding the apostrophe forces it to text

 

[Rick Rothstein]

So, I'm looking for a script that simply will undo the the date recognize. And no, this can't be any manual work of cell formattings etc. since I have accounting data of thousands of rows that comes from a source file, that might already be "infected".

Basically the function could be something like

Function reverseidiotictranslation(infecteddata As String)        
    Dim result As Double
    result = whateverthetranslationfunctionactuallyis(infecteddata)
    reverseidiotictranslation = result
End Function

Not 100% sure, but I think this may do what you are looking for. If the value passed in is a date, it puts the month as the whole number and the two-digit year as the decimal part of the number. If you pass in a non-date, it is returned to you unchanged.

Function DateToDouble(V As Variant) As Variant
  If IsDate(V) Then
    DateToDouble = Format(V, "m\.yy")
  Else
    DateToDouble = V
  End If
End Function

 

[Jaymond Flurrie]

Not 100% sure, but I think this may do what you are looking for. If the value passed in is a date, it puts the month as the whole number and the two-digit year as the decimal part of the number. If you pass in a non-date, it is returned to you unchanged.

Function DateToDouble(V As Variant) As Variant
  If IsDate(V) Then
    DateToDouble = Format(V, "m\.yy")
  Else
    DateToDouble = V
  End If
End Function

Otherwise this works, but I _think_ this limits the languages i.e. if the source file was "corrupted" with Finnish Excel and the target system (the reader which has this code) is English Excel, it wouldn't work?

 

[Jaymond Flurrie]

so a find replace of heinä. to 'jul., thats 12 events over the whole file ? and adding the apostrophe forces it to text

I can do that, but can you tell with 100% certainty that the only thing I need to do is to replace those and to replace those with the order number of the month and nothing else? This is customer data and I definitely can't afford a screw up here.

 

[mole999]

I can't swear 100% but use a copy, try it and see if you get usable data, the worst that could happen is you damage a bad copy

 

[Rick Rothstein]

Otherwise this works, but I _think_ this limits the languages i.e. if the source file was "corrupted" with Finnish Excel and the target system (the reader which has this code) is English Excel, it wouldn't work?

I don't do international programming (only US locale), so I don't know for sure, but I would think if the value is a real date (not a text string) in the computer's locale, then I would expect my code to work. Is your "date" a real date or simply a text string? I was under the impression it was a real date because 11.84 became Nov 1984 (or whatever the month name in your locale is)... it should only do this if Nov 1984 is a real date.

 

[Jaymond Flurrie]

I don't do international programming (only US locale), so I don't know for sure, but I would think if the value is a real date (not a text string) in the computer's locale, then I would expect my code to work. Is your "date" a real date or simply a text string? I was under the impression it was a real date because 11.84 became Nov 1984 (or whatever the month name in your locale is)... it should only do this if Nov 1984 is a real date.

It's a real date (well, not a date since Nov 1984 is just time frame, not date - at least that's how I see it). I obviously could program in the both language versions that I can expect the user to have and add there if needed.

Is there any kind of documentation from Microsoft that how this their genius data-corruption system works?