Excel geeft datum in US vorm weer/beinvloedt mijn IF formule

[mveger]

Voor een gedeeld document (met collega's in verschillende landen en dus datum-weergaves, op een Sharepoint pagina), met projecten, start datum, eind datum en project duur, wil ik een vrij simpele Status kolom invoeren. Projecten die actief zijn (i.e. end date later dan vandaag, 'Active' tekst geven, en projecten die al beëindigd zijn met dus een einddatum die in het verleden ligt 'Ended' tekst geven.

Ik gebruik de volgende formule:
=IF(ISBLANK(R53),"",IF(R53<TODAY(),"Ended","Active"))
gebaseerd op: https://www.extendoffice....e-if-date-has-passed.html

Het klopt echter niet en dit komt volgens mij door de datum weergave die US en Europees door elkaar haalt. Wanneer ik een datum invoer, ik nog telkens de US versie te zien krijg; ik typ 3-5-2021 en het geeft weer 05/03/2021, als ik het dan naar uitgeschreven format omzet wordt het 05 March 2021.

Gecheckt, en TODAY is wel gewoon de datum van vandaag, geschreven als 22/02/2021.
Hoe verhelp ik dit probleem?

[Belindo]

Excel icm. verschillende datumnotaties is nooit mijn favoriet geweest.

Waarom 3-5 uiteindelijk 5 maart wordt, en 22-2 niet, is omdat Excel 22-2 niet kan omzetten in de 2e van de 22e maand, want dat bestaat niet. Dus doet ie een fallback naar 22 februari.

Als je 100% zeker bent dat al je datums in US formaat zijn (dan zou je sommige datums in jouw lokale notatie moeten zien, maar dan dag en maand omgedraaid, en sommige andere datums zou je als US formaat, met slashes moeten zien), dan kun je door middel van een hulpkolom de datum corrigeren.

Onder water is een échte datum in Excel een nummer (het aantal dagen sinds 1-1-1970), dus door middel van ISNUMBER() kun je kijken of Excel een datum als échte datum heeft staan. In dat geval kun je de datum opnieuw berekenen door middel van DATE() waarbij je de dag een maand omwisselt. Standaard is het DATE(Year, Month, Day), maar je moet dan DATE(Year, Day, Month) doen. Het jaar, de maand en de dag haal je uit je bron-datum met YEAR(), MONTH() en DAY()

Dan hou je de datums over die níet als nummer worden herkend. Deze zul je door middel van LEFT(), RIGHT() en MID() moeten veranderen.

Bijvoorbeeld: 3/5/2021 kun je naar een datum omzetten door DATE(2021,3,5). De makkelijke methode voor de maand is in dit geval LEFT(JeCel,1), maar soms wil je ook de twéé linker karakters. Je zult dan creatief aan de slag moeten met kijken of je één of twee karakters nodig hebt.

Ik vul dit zo even aan met een voorbeeldje.

[Belindo]

Even met plaatjes. Je hebt de volgende data:

De groene worden door Excel gezien als een datum, echter met de maand en dag omgedraaid. De rode worden niet gezien als datum (want de formatting wordt niet toegepast).


Voor de groene datums kun je door middel van DATE(YEAR(Cel),DAY(Cel),MONTH(Cel)) de datum goed krijgen. Zoals je ziet draai ik MONTH(Cel) en DAY(Cel) om in de DATE() formule. Als extra stap gebruik ik een hulpkolom om te kijken of het een datum is dmv. ISNUMBER(Cel) Deze hulpkolom kun je later gebruiken om je twee manieren van omzetten te doen.



Voor de overige datums moet ik eerst het jaar en de dag een maand uit de 'tekst' halen. In mijn voorbeeld is het makkelijk, want de maand heeft een leading zero, en is dus altijd twee karakters lang. Voor maand pak je dus de twee linker karakters, voor jaar pak je de twee rechter (en doe je +2000 omdat het anders als 1917 wordt gezien). En voor de dag pak je de middelste twee karakters, startend op karakter 4.

Vervolgens kun je een juiste datum krijgen door middel van DATE(Jaar, Maand, Dag) waarbij je óf verwijst naar je hulpkolommen, of de formule ervan direct in je DATE() formule gebruikt.

Mogelijke pitfalls zijn wanneer de maand geen leading zero heeft, maar mei als 5 en October als 10 wordt geschreven. Je kunt dan bijvoorbeeld alsnog LEFT(Cel,2) gebruiken, maar moet dan voor mei die slash weer weghalen, bijvoorbeeld met SUBSTITUTE(), óf je zoekt met FIND() de positie van de eerste slash op en retourneert dan het aantal karakters van die positie minus 1 (bijvoorbeeld; FIND(Cel,"/") geeft je voor mei het getal 2 (want op die positie staat de slash) en voor october geeft ie 3. Je wilt dus het linker aantal karakters gelijk aan de positie van de slash, minus 1.

Uitendelijk kun je al je hulpkolommen overslaan en direct een formule schrijven zoals:

code:

=IF(ISNUMBER(Cel),Je eerste formule, je tweede formule)

ofwel

code:

=IF(ISNUMBER(Cel,DATE(YEAR(Cel),DAY(Cel),MONTH(Cel)),DATE(RIGHT(Cel,2)+2000,LEFT(Cel,2),MID(Cel,4,2)))

[Lustucru]

Al die hulpkolommen lijken mij een slecht idee: je werkt om het probleem heen. Om te begrijpen wat er gebeurt moet je drie dingen uit elkaar houden:
- de opgeslagen waarde
- de weergave
- het parsen van de invoer.

1: Als de opgeslagen waarde een datum(getal) is, zal je formule correct werken.
2: De weergave wordt of automatisch bepaald aan de hand van regioinstellingen op je systeem, of het wordt vast gedefinieerd in de celeigenschappen.
3: Het parsen van de invoer doet excel aan de hand van de regioinstellingenen. Ik weet niet of Excel ook kijkt naar de weergave instellingen van de cel. Lukt het parsen niet dan zal Excel geen datum opslaan maar een tekst. De vergelijking of een tekst kleiner is dan vandaag gaat niet lekker.

De oplossing zou wat mij betreft zijn om de weergave zo in te stellen dat er geen twijfel mogelijk is over de ingevoerde datum, bv MMM dd yyyy. De maand wordt dan in tekst weergegeven. Je kunt nog altijd 5-2-19 invoeren, maar je ziet meteen of dat wordt geparsed als May 2 2019 of als Feb 5 2019.

Om het af te maken zet je een validatie op de datumkolom, zodat de gebruiker een popup krijgt als de ingevoerde tekst geen geldige datum is.

edit:

Dat lost dus niks op voor bestaande invoer... die zul je dus idd handmatig moeten parsen naar de juiste datum, en daar ligt idd '=datum(jaar,maand,dag)' voor de hand zoals @Belindo beschrijft.

[Voor 9% gewijzigd door Lustucru op 22-02-2021 12:59]