Excel: datums worden steeds niet herkend als datum

[Smithkj]

Mijn vraag
Ik ben bezig met een analyse in Excel; hiervoor heb ik een dataset die opgebouwd is uit 2 databronnen en samengevoegd is en aangeleverd door 3 partijen.

ik wil in een draaitabel graag aantal per jaar / kwartalen inzichtelijk maken.

deze dataset bevat datums: nu zijn de datums uit bron A perfect in orde (dus draaitabel groepeert ze netjes). Maar de datums uit Bron B blijft ie op één of andere manier niet als datum zien. en bijvoorbeeld voor 2 partijen ziet ie '1900' als twee unieke jaren.


Relevante software en hardware die ik gebruik
Excel office 365


Wat ik al gevonden of geprobeerd heb
- uiteraard alle formatting aangepast naar datum en in allerlei smaken en soorten.
- datums splitsen naar dag, maand, jaar en hier een nieuw serieel getal van maken. hieruit zag ik dat bij één aanleverende partij de datums intact bleven en geen serieel getal werden. dat heb ik gecorrigeerd. zodat er één lijst met seriële datums is.
- daarna via '=DATUM' jaar, maand en dag weer toegevoegd.

helaas zonder resultaat.

opvallend is dat in de dataset de datums die wél werken het format 'datum hebben - taal Armeens'. dit heb ik ook gekopieerd naar de niet werkende kolom: helaas zonder resultaat.

heeft iemand nog een denkrichting?

Alvast bedankt!

[g0tanks]

Kan je voorbeelden (screenshots of bestand uploaden) laten zien van data uit bron B? Het liefst 'ruw' dus zonder opmaak. Nu is het best moeilijk om er iets over te zeggen.

[F_J_K]

Dat het via datum() terugzetten mislukt verbaast me als de ‘bron’ steeds ‘nette’ jaar, maand, dag is. Inderdaad misschien goed om screenshots te doen.
Let op dat je de kolom met de datum() formules nieuw hebt aangemaakt en de juiste datumformat geeft.

Hoe importeer je de twee maal drie sets? Als copypaste, doe dat met alleen waarden kopiëren zonder opmaak.

Is het overigens een eenmalige actie, of gaat het vaker gebeuren? In dat tweede geval kan je beter de dialoog aan gaan met de aanleverende partij(en) die het incorrect doen.

[Will_M]

Excel in combinatie met DATE / DATUM. Altijd leuk als je met 'vertaalde' sheets aan het werk bent

[Smithkj]

Hieronder zie je de al (gesplitste) en weer samengevoegde datum. bij de kolommen verder in de dataset (rechts) staat dezelfde formatting en daar werkt het wél goed. dus het zit in deze specifieke kolom.



Onderstaande afbeelding zie je het probleem denk ik: in eerste instantie zag ik al 2 formats ontstaan, dat heb ik rechtgetrokken. maar toch blijft er iets hardnekkigs inzitten: hij ziet 2 aparte '1900' als jaar.



Hieronder zie je in kolom C hoe de aanlevering van de 2 partijen eruit ziet. nogmaals: alles is serieel gelijk en identiek.


Wat betreft structureel aanleveren; dit is voor nu eenmalig. als er een structureel karakter komt wil ik ook snel weg van Excel

[Bolletje]

Ik zie het probleem niet?

Het is 1900 en 1990?

De 1-1-1900 komt van date = 0.

[Smithkj]

in dit geval niet. want het is datum 1-1-1900 (serieelnummer = 1). Maar dit zijn maar twee voorbeelden, in de hele dataset 'herkent' Excel dus datums:

1. Er hetzelfde uitzien
2. dezelfde datum vertegenwoordigen
3. Qua 'format' ook identiek zijn

als twee unieke waarden. daarmee kun je ze dus helaas niet groeperen in een draaitabel.

[Bolletje]

Kan je die beide datums eens converteren naar een nummer?

Misschien zit er nog een time element in. Dan is het geen integer

[Tazzios]

Uitgaande dat dag en maand altijd 2 notatiecijfers hebben:

dag: =LINKS(A1;2)
maand: =RECHTS(LINKS(A1;5);2)
jaar: =RECHTS(A1;4)
cellen omzetten naar datum: =DATUM(D1;C1;B1)

in 1 formule: =DATUM(RECHTS(A1;4);RECHTS(LINKS(A1;5);2);LINKS(A1;2))

[g0tanks]

g0tanks schreef op woensdag 2 februari 2022 @ 13:43:
Het liefst 'ruw' dus zonder opmaak. Nu is het best moeilijk om er iets over te zeggen.

Hoe zien de data eruit zonder opmaak? Dus gewoon als nummer zoals @Bolletje vraagt.

[Microkid]

Die 1900 komt door een ongeldige datum. Het originele veld zal dus niet kloppen.

[gambieter]

Voor dit soort dingen gooi ik ze altijd even via Notepad++ en een schone cel. Excel en datums, breek me de bek niet open, alhoewel het meestal is dat iets dat geen datum is omgezet wordt in een datum, zonder waarschuwing.

Ik heb zelfs een feature request gedaan om dat eruit te slopen, of uitschakelbaar te maken.

[Smithkj]

Thanks voor al jullie reacties: ik heb t als integer al geprobeerd: dat was verschillend(al voor mijn post), maar nu niet meer!

Ik ga morgen nog even de andere tips proberen, thanks!

Update:
Notepad geprobeerd: dat werkt helaas niet. via Notepad, als tekst opgeslagen, naar CSV en dan in de Excel geplakt. ook de formule aanpassen is niet gelukt helaas.

aangezien het ook een 'pilot' is nu gekozen om het te laten voor wat het is helaas. dan maar wat omslachtiger e.e.a. in kaart brengen, blijft heel erg raar

[ Voor 47% gewijzigd door Smithkj op 05-02-2022 20:52 ]

[MustangV8]

Ik had hetzelfde probleem en heb het zo opgelost: best een nieuwe kolom maken ernaast met de formule =KOLOMx *1 en die als datum formatteren. Ergens loopt het mis bij de formattering in de achtergrond. De oorspronkelijk kolommen komen uit een database en waren als ALGEMEEN geformatteerd, simpel het format naar DATUM veranderen hielp ook niet. Enkel de formule werkte. En ja die "vertalingen" van formules en formattering is een ramp in Excel.