[Excel 2003] Datevalue in formule gaat niet altijd goed *

[Da_maniaC]

Hi all,

Ik ben een klein tooltje in elkaar aan het zetten zodat ik een vergelijk kan maken tussen 2 verschillende data.
(Dit doe ik in een Excel sheet met een macro, maar ik zal de werking/formule in essentie uitleggen).

Ik heb Datum1 (Kolom F) en Datum2 (Kolom J), vervolgens wil ik met een formule een signalering hebben wanneer Datum1 groter is dan Datum 2. Ik gebruik hiervoor de volgende formule in Kolom M:
Excel: =IF(DATEVALUE(F2)>DATEVALUE(J2),"THT Break","")
VBA: "=IF(DATEVALUE(F2)>DATEVALUE(J2),""THT Break"","""")"

Wat blijkt nu...de formule werkt goed echter gaat het soms spontaan fout (zie bijvoorbeeld Regel 25 en 26 in de onderstaande screenshot).
Ik heb inmiddels het bovenstaande scenario handmatig nagebootst. De formatting van de datum cellen is identiek aan die van alle andere data (waar het wel goed gaat). Toch, gaat de berekening verkeerd en krijg ik een #VALUE! error.

Heeft iemand misschien enig idee waarom het 99,9% van de keren goed gaat en dan opeens niet meer?

Zie eventueel nog een screenshot van de sheet:

[defusion]

In je voorbeeld iig is de datum niet goed uitgeschreven, 2013 vs gewoon 13

(Of heb je dit express gedaan om het resultaat na te bootsen?)

[Da_maniaC]

De 'format' van alle cellen in Kolom F en J is hetzelfde (dd-mm-yyyy;@).
Ik snap daarom niet waarom er in die cellen "d-mm-yy" lijkt te staan.
Wellicht dat de berekening in Kolom M hierdoor fout gaat.....maar ik snap niet waarom de formatting afwijkt.

[Reptile209]

Die specifieke datums gaan mis (of misschien juist eigenlijk goed), omdat ze in NL en in US formaat een datum zijn. Ik denk dat al je datums eigenlijk als string worden behandeld (met datevalue 0, dus dat geeft geen probleem in je script) en dat alleen die twee echt als datum worden geparsed (de eerste als 3 feb, de 2e als 9 feb). Doe - ter controle - eens een celformatting als 01-jan-14, dan zie je het direct.

Zorg dus even dat je lokale datumsettings (Windows en/of Excel internationale instellingen) kloppen met je invoer, dan gaat het goed .

[Lustucru]

Het is eerder andersom: alleen de cellen waar het fout gaat zijn datums; de overige zijn strings die correct als datum kunnen worden geparsed. Datumwaarde() of datevalue() accepteert alleen een string als input. Een gokje: de formattering van de cellen is aangepast nadat ze geimporteerd/ingevuld zijn?

Je kunt het nagaan door de formattering te wijzigen in 'numeriek'. Je zult zien dat alleen op de foute regels de datums worden gerepresenteerd door een nummer. Om te datums te vergelijken kun je datevalue niet gebruiken: correct is dan het simpele als(F2>M2;;)

Of nadersom: wijzig het format van de cellen op de foute regels in tekst, en tik de datum opnieuw en voluit in (bv 13 mrt 2012). De foutmelding verdwijnt.

[ Voor 42% gewijzigd door Lustucru op 29-02-2012 08:22 ]

[defusion]

mogelijk dat het systeem staat ingesteld op amerikaanse datums. dan zullen datums als 31-1-2013 niet gepakt worden omdat er geen 31e maand is.

2-08-2013 kan wel, maar dat wordt dus als m-dd-yy omgezet naar een datum door excel. (dus 8 feb 2013 voor ons)

[pedorus]

defusion schreef op woensdag 29 februari 2012 @ 08:50:
mogelijk dat het systeem staat ingesteld op amerikaanse datums. dan zullen datums als 31-1-2013 niet gepakt worden omdat er geen 31e maand is.

Gezien het screenshot is dat enkel hier niet het geval. De verklaring met dat het al datums (dus numerieke waardes zijn) lijkt me het meest logisch. Een hele simpele workaround is IFERROR(DATEVALUE(F2),F2) gebruiken, maar beter is het om datums gewoon overal als datum in de cellen te hebben staan, zodat je DATEVALUE niet nodig hebt.

Er tussenin zit een oplossing met test als

code:

=IFERROR(DATEVALUE(A1),IF(AND(YEAR(A1)>2000,YEAR(A1)<2100),A1,NA()))

als je weet dat correcte datums in de jaren 2001-2099 vallen. Ook niet mooi, maar iets minder probleemgevoelig.

[Da_maniaC]

Whoo, dank voor alle reacties mensen.

De sheets die ik gebruik voor de data invoer worden inderdaad na het inkopiëren nog geformatteerd.
Ik pas dan een harde formattering toe van 'dd-mm-yyyy;@'. Dit zou ik dus niet moeten doen?

Zou het een idee zijn om te zorgen dat de formatting van al mijn sheets met datum bijv. '31-jan-2012' wordt?

[defusion]

Da_maniaC schreef op woensdag 29 februari 2012 @ 13:00:
Whoo, dank voor alle reacties mensen.

De sheets die ik gebruik voor de data invoer worden inderdaad na het inkopiëren nog geformatteerd.
Ik pas dan een harde formattering toe van 'dd-mm-yyyy;@'. Dit zou ik dus niet moeten doen?

Zou het een idee zijn om te zorgen dat de formatting van al mijn sheets met datum bijv. '31-jan-2012' wordt?

Die formattering zou je inderdaad kunnen aanpassen, dan zie je makkelijk welke velden niet gepakt worden. ook kun je je niet vergissen als er "jan" staat, dan weet je 100% zeker dat dat de maand is.
Als er "1" staat kan excel het verkeerd interpreteren (zoals gezegd: op z'n amerikaans)

[Da_maniaC]

Alright, vanwege het feit dat de invoer een stel .csv bestanden zijn ben ik toch voor de iets minder mooie methode gegaan.
Ik heb eerst de volgende module toegevoegd:

code:

Function IFERROR(ToEvaluate As Variant, Default As Variant) As Variant
'Duplicates Excel 2007 functionality (UDF)
    If IsArray(ToEvaluate) Then
        IFERROR = IIf(IsError(ToEvaluate(1)), Default, ToEvaluate)
    Else
        IFERROR = IIf(IsError(ToEvaluate), Default, ToEvaluate)
    End If
End Function

Vervolgens heb ik de volgende formule gebruikt:
VBA: "=IF(IFERROR(DATEVALUE(F2)>DATEVALUE(J2),F2>J2),""THT Break"","""")"

Het lijkt nu goed te gaan.

[F_J_K]

Da_maniaC schreef op woensdag 29 februari 2012 @ 14:34:
het feit dat de invoer een stel .csv bestanden zijn

Hoe staan op de machine waar de import naar Excel gebeurt de internationalisatie-settings? En hoe staan de datums in de csv?

Eventueel kan je de csv zodanig importeren dat je dag, maand en jaar los importeert en achteraf pas weer samenvoegt.

[Lustucru]

Da_maniaC schreef op woensdag 29 februari 2012 @ 13:00:
Zou het een idee zijn om te zorgen dat de formatting van al mijn sheets met datum bijv. '31-jan-2012' wordt?

Het beste idee is uiteraard om naar de bron te gaan en te controleren hoe datums daarin worden weergegeven. Vervolgens kun je tijdens je import keihard aangeven of die kolom als datum of als tekst moet worden ingelezen.

[Da_maniaC]

@ F_J_K / Lustucru:
In de eerste instantie opende mijn macro een paar csv files en deed vervolgens Copy -> Paste special: values.
Dus ik kan me nu voorstellen waarom ev. formatting verloren ging.
Eventuele datum conflicten kan ik buiten excel in ieder geval niet oplossen. De csv files komen uit een Cognos query die vervolgens weer gevuld wordt met WMS gegevens dmv een relativity interface.
Ik weet nu iig dat het bij de datum formatting te zoeken is. Thanks.

[F_J_K]

Dus ik kan me nu voorstellen waarom ev. formatting verloren ging.

Punt is dat de formatting v.w.b. volgorde van de drie getallen (d,m,j) niet verandert.

csv is eigenlijk een simpel tekstbestand. Kijk met kladblok hoe het "fysiek" in het bestand staat en vergelijk dat met hoe je het importeert. Check daarbij ook de regional settings van Windows en VBA. Best kans dat de csv uit gaat van mm/dd/yyyy terwijl Windows dd/mm/yyyy verwacht.
Als de csv inderdaad mm/dd is en je niet de Windows-instellingen wilt aanpassen kan je bijv. bij de import expliciet aangeven dat je het als tekst wilt importeren. Dan kan je in Excel vervolgens manipuleren hoe je wilt.
Dat kan dan bijv met dag in B1: =DEEL(A1;4;2), maand in C1: =LINKS(A1;2), jaar in D1: =RECHTS(A1;4) en dan datum: =DATUM(D1;C1;B1)
Of in je macro-code de veldvolgorde omgooien, kan natuurlijk ook prima.

Als de csv al is wat de import verwacht zou dit niet nodig zijn, dan is er wat anders aan de hand. Het zou overigens ook nog wel eens kunnen dat het via VBA wat dichter bij Amerikaans blijft.

Als het werkt werkt het, maar je huidige oplossing is niet zo mooi. Maar dat moet je natuurlijk zelf weten

Edit: zie idd onder, daar doelde ik op met Amerikaans via VBA.

[Verwijderd]

het is subtieler dan dat.

ik heb het al eens eerder gepost, het importeren van csv-bestanden via vba verloopt altijd met us-regio instellingen. wanneer je de macro opneemt verloopt alles correct. voer je hem opnieuw uit, loopt het fout.

zoals je kan zien in de TS-afbeelding werden (in de context van de gebruiker) datums toevallig correct als tekst geïmporteerd omdat de dag van de geïmporteerde datums boven 12 lag en dus niet als maand beschouwd kon worden. voor de 2 regels die wel als datum geimporteerd werden, zijn dag en maand omgewisseld! een voorbeeldje :

datums.csv in d-m-j volgorde :

code:

01-02-2011
12-02-2011
13-02-2011

resultaat importeren via VBA/macro :
02/01/2011, dag maand omgewisseld, geimporteerd als datum en dus onjuist
02/12/2011, dag maand omgewisseld, geimporteerd als datum en dus onjuist
13-02-2011 : geimporteerd als tekst, ok voor gebruik in formule datumwaarde/datavalue