In deze blog ga ik het hebben over de opmaak van data.
Niet de opmaak die je via cel-opmaak kan instellen, maar de opmaak die je letterlijk moet ingeven als je met de ‘TEXT’-functie werkt. De opmaak die je daar gebruikt, werkt enkel als de eindgebruiker in dezelfde taal werkt als de taal die jij hebt ingesteld.
Eerst even de situatie schetsen waarin dit probleem zich stelt.
Ik ga uit van een Engelstalige gebruiker die via de cel-opmaak een datum weergeeft als:
“Mon 12 jan 2019”
Hiervoor moet de gebruiker de volgende code ingeven voor de cel-opmaak:
“ddd dd mmm yyyy”
Een Franstalige gebruiker kiest voor “jjjj jj mmm aaaa” en een Nederlandstalige gebruiker kiest voor “ddd dd mmm jjjj”. Wanneer zij hun bestand uitwisselen met elkaar zal Excel automatisch de vertaling van de gebruikte codes aanpassen aan de taal van de ontvanger.
Tot daar de standaard werking van Excel.
Ditzelfde principe geldt niet meer als een cel wordt opgesteld met de ‘TEXT’-formule. Hierover gaat deze blog.
Indien je een datum wenst op te maken op basis van een formule in plaats van via de standaardopmaak ga je als volgt te werk.
Plaats een datum in cel A1.
Tip: CTRL ; geeft je automatisch de datum van vandaag !
Typ in A2 de volgende formule (in deze blog zijn de formules steeds Engelstalig)
= TEXT (A1 ; “ddd dd mmm yyyy”)
Hierbij bots je al direct op het probleem waar deze blog over gaat. De formule zal enkel werken als je toestel in het Engels is ingesteld. Excel gaat de vertaling van Y (voor UK) naar J (voor NL) of naar A (Voor FR) niet maken. Je hebt de code trouwens letterlijk als tekst in de formule geplaatst.
Is je computer op Nederlands ingesteld, dan typ je = TEXT (A1 ; “ddd dd mmm jjjj”) en indien je computer op Frans staat ingesteld, typ je = TEXT (A1 ; “jjj jj mmm aaaa”)
Als de Engelstalige gebruiker zijn bestand naar de Franstalige collega doorstuurt, zal de datum er als volgt uitzien: ”Tue 01 jan yyyy”. De Franstalige instelling begrijpt de code ‘Y’ niet en geeft ze weer alsof het de bedoeling was om deze tekst achter de datum weer te geven.
Dit probleem gaan we oplossen door een kleine referentietabel aan te maken. Hierin gaan we elk gekend formaat, of eigenlijk elke gekende gebruikerstaal, trachten te herkennen. Aan de oplossing daarvan koppelen we dan het gewenste formaat.
Bouw een tabel die er als volgt uitziet:
Geef een vaste datum in. In het voorbeeld is dat gebeurd in cel D2. De opmaak van deze cel is niet belangrijk, al geef ik zelf de voorkeur aan ‘number’ zonder decimalen.
In deze tabel gaan we achterhalen of de computer van de gebruiker in het Engels, Nederlands of Frans staat ingesteld.
- Geef in D4 volgende formule in: =TEXT($D$2;”dd-mm-jjjj”)
- Geef in D5 volgende formule in: =TEXT($D$2;”jj-mm-aaaa”)
- Geef in D6 volgende formule in: =TEXT($D$2;”dd-mm-yyyy”)
Voorlopig zie je volgende oplossing
De ‘TEXT’-formule geeft de datum weer in het gewenste formaat maar het resultaat wordt echt als een tekst beschouwd in plaats van als een datum. In bovenstaand voorbeeld kan je besluiten dat de gebruiker zijn computer ingesteld heeft met als locatie België of Nederland.
Deze formules geven dus een tekst als resultaat en de uitkomst van de formule kunnen we niet evalueren met een andere formule.
Daarom gaan we de bekomen datum (of de 2 andere verkeerde cellen) vermenigvuldigen met de waarde 1. Hierdoor gaat Excel de bekomen oplossing van de TEXT-formule als een numerieke waarde tonen. Nu kan je de opmaak van de cel best als ‘number’ instellen.
=TEXT($D$2;”dd-mm-jjjj”)*1
Je merkt dat de 2 cellen die niet voldoen aan het gebruikte datumformaat niet naar een getal kunnen worden omgezet.
Uit testen is gebleken dat bij het openen van dit bestand niet direct het correcte taal-formaat wordt herkend omdat Excel zijn berekeningen niet uitvoert bij het openen van het bestand. Dit gaan we omzeilen door het getal 1 te vervangen door ‘RANDBETWEEN(1,1)’. Hierdoor wordt Excel bij het openen van een bestand verplicht deze cellen uit te rekenen.
De formule ziet er nu als volgt uit.
=TEXT(D2;”dd-mm-jj”)*RANDBETWEEN(1;1)
In de laatste kolom zetten we de gewenste opmaak-tekst per taal.
De cel E2 boven deze tabel gaan we nu van een formule voorzien.
De ‘Index’-formule analyseert de blauwe zone met de verschillende gewenste formaten. Om te bepalen welke rij we in dit gebied nodig hebben gebruiken we de ‘Match’-formule. We controleren waar we de ingegeven datum ‘D2’ terugvinden in de paarse zone. Nu weet de ‘Index’-formule in welke rij onze gewenste oplossing staat. De ‘Index’-formule verwijst verder nog naar kolom 1 aangezien we niet meer 1 kolom als basis hebben ingezet.
In de cel ‘A2’ kan je nu de volgende formule ingeven:
= TEXT( A1 ; E2)
Je kan de cel E2 nog een naam geven. Zelf heb ik daarvoor “nFormat” gebruikt. De formule ziet er dan als volgt uit:
= TEXT( A1 ; nFormat)
Om dit te testen dien je Excel te sluiten en de instellingen van je computer te wijzigen naar een andere taal of basislocatie. En als je geluk hebt zit er een anderstalige collega in de buurt.
