excel sorteren op datum met macro

[HvdLaan]

Mijn vraag
Goedenavond. Ik heb een excelblad met in kolom A de datum/tijd. Deze wordt echter niet in juiste formaat aangeleverd:

07.03.2025 13:25

Ik wil het blad sorteren op de datum in kolom A. Handmatig gaat dit goed. Ik vervang de puntjes door een streepje, daarna kan ik sorteren op kolom A. Nu wil ik dit met een macro gaan doen. Is dit mogelijk? Ik heb geen verstand van programmeren. Met de knop "macro opnemen" gaat dit niet goed. Ik krijg het volgende resultaat:



Dit is de code die gemaakt wordt:



Is hier een oplossing voor?


Relevante software en hardware die ik gebruik
...Excel 365

Wat ik al gevonden of geprobeerd heb
...

[pagani]

Hulpkolommetje maken waarin je met een normale functie de puntjes vervangt. Ik zou voor zoiets simpel wegblijven van macro's.

[sig69]

En je datum formaat staat niet goed.

[m-vw]

De macro doet het goed, maar je bent gestuit op één van de, zo niet de grootste ergenis binnen Excel. Datums.

Je ziet dat er een verschil is tussen data met enkel getallen t/m 12 en data met nummers boven 12.

[HvdLaan]

@ pagani Het originele blad dat ik aangeleverd krijg bevat nog veel meer kolommen die ik allemaal moet rangschikken. Het sorteren op datum is slechts het sluitstuk van al deze handelingen. Vandaar dat ik al deze maandelijkse handelingen met een macro wilde doen.

@m-vw Dat is een goeie. Dit zijn allemaal datums van het eerste kwartaal. Dus maand jan-febr en mrt.
Dus dan zal het wel te maken hebben met de Amerikaanse datum notering.

Raar dat het handmatig wel goed gaat.

[dixet]

Ik meen inderdaad dat zodra je bewerkingen via VBA doet hij default naar Amerikaanse instellingen in plaats van je lokale instelling. Lekker irritant.

Wat je kan doen - en wat tegenwoordig sowieso mijn voorkeur boven VBA heeft - is de gegevens aanpassen via het tabblad Data, en dan From Table/Range. Je komt daarmee in de PowerQuery editor die veel genuanceerdere aanpassingen toestaat, inclusief zelf aangeven in welk formaat je datums staan.

Waarschijnlijk kan je alle handelingen die je noemt in PowerQuery kwijt, dus blijft het net als in VBA een automatisch proces.

[HvdLaan]

Heren,
Bedankt voor het meedenken. Dan zit er niets anders op om om de datum notatie heen te werken.
Ik heb de datumnotatie eerst met "tekst naar kolommen" uit elkaar gehaald. Daarna gesorteerd en vervolgens weer samengevoegd. Beetje omslachtig maar het werkt gelukkig wel.

code:

[Pindakaas]

Klinkt heel moeilijk allemaal. Kan je niet gewoon een hulpkolom maken met =Datum() ?

[Mickey77]

HvdLaan schreef op zondag 16 maart 2025 @ 14:22:
Heren,
Bedankt voor het meedenken. Dan zit er niets anders op om om de datum notatie heen te werken.
Ik heb de datumnotatie eerst met "tekst naar kolommen" uit elkaar gehaald. Daarna gesorteerd en vervolgens weer samengevoegd. Beetje omslachtig maar het werkt gelukkig wel.

[Afbeelding]

code:

Ik zou nog even goed controleren of het ECHT goed gaat. Het probleem met datums in Excel zit namelijk niet alleen in het eerste kwartaal. Het speelt in elke maand, als je een datum hebt in de eerste 12 dagen dan heb je kans dat de dag en maand wordt omgedraaid. Dus mogelijk denk je dat het werkt omdat het er goed uit ziet, maar worden dagen en maanden toch omgedraaid!

Als het mogelijk is, is mijn gewoonte om zoveel mogelijk te werken in de notatie yyyy-MM-dd. Dan vermijd je het probleem dat dagen en maanden omdraaien. Dus als je een systeem hebt dat data uitvoert, om het later te importeren... dan zou je kunnen proberen om het in dit formaat te exporteren. Dan heb je goede kans dat ook de import goed gaat. Ook als het tekst blijft, dan heb je zelf zekerheid over wat dag en maand is.

[HvdLaan]

@pindakaas Zoals jullie wel begrepen hebben ben ik maar een beginner met excel en vooral met macro's.
Om de gespitste datums weer samen te voegen is "=Datum()"inderdaad wel een behoorlijk stukje efficienter

@Mickey77 Op de export heb ik helaas geen invloed. Ik ga nog eens goed controleren of alles goed gaat.

[Pindakaas]

HvdLaan schreef op zondag 16 maart 2025 @ 14:45:
@pindakaas Zoals jullie wel begrepen hebben ben ik maar een beginner met excel en vooral met macro's.
Om de gespitste datums weer samen te voegen is "=Datum()"inderdaad wel een behoorlijk stukje efficienter

@Mickey77 Op de export heb ik helaas geen invloed. Ik ga nog eens goed controleren of alles goed gaat.

Ik zou voor zoiets als dit macro's proberen te vermijden. Als je een hulpkolom maakt, daar =datum() gebruikt en met =links() =rechts() =vind.spec() =lengte() probeertje de goede waardes er in te krijgen werkt dit een stuk stabieler

[Mickey77]

@Pindakaas Een goede oplossing, maar dat werkt natuurlijk alleen voor de cellen waar een tekstwaarde in staat. Als ik de eerste afbeelding zie, dan wordt een deel van de waarden van kolom A als tekst gezien. Als alles in Kolom A een tekstwaarde zou hebben, zou ik dezelfde oplossing kiezen als jij adviseert.

Maar als ik het goed zie, dan wordt een ander deel van de data na het openen omgezet naar een datum. Daarbij is het zeer de vraag of die datum wel juist is. Zo zie ik helemaal boven in de topicstart de tekstwaarde "07.03.2025 13:25" en in de afbeelding zie ik de waarde 03-07-2025 13:25. Goede kans dus dat er een datum in A1 staat waarbij dag en maand zijn omgedraaid. Laten we zeggen 50% kans

@HvdLaan Verander de notatie van de datum in kolom A eens in een number format waarbij de maand in letters wordt weergegeven. Dan is de vraag of er 7-Mar-2025 komt te staan of 3-Jul-2025. Controleer dan of alle combinaties van dag en maand <12 en >12 overeen komen met het bestand dat je importeert.

[HvdLaan]

Dat scheelt inderdaad een behoorlijk wat stappen:)
Klopt dit zo een beetje?

[Mickey77]

Ja als dit is wat @Pindakaas en ik bedoelen. Als als dit data zijn in januari dan ziet dit er goed uit

Kennelijk heb je het zo kunnen regelen dat alle waarden in kolom A importeren als teksten, dan is dit een prima oplossing.

[ Voor 4% gewijzigd door Mickey77 op 16-03-2025 15:42 ]

[HvdLaan]

De import die ik krijg zijn de datums inderdaad (allemaal) tekstformaat, met die rottige puntjes er tussen.

In het begin heb ik een macro gedraaid die de puntjes vervangt door streepjes.
Dit gaat handmatig wel goed maar met de macro niet. Ik krijg dan gedeeltelijk tekst en gedeeltelijk datums terug, zoals ook te zien in mijn eerste afbeelding.

De oplossing van pindakaas werkt zo te zien goed. Ik ga nog eens uitproberen met datum van het hele jaar.

Thanks

[Mickey77]

Nu begrijp ik dat het feit dat een deel een datumwaarde werd en een deel een tekstwaarde werd, pas optrad NAdat je de punten door komma's hebt vervangen. Dat alles gewoon netjes een tekst blijft na het openen, is goed nieuws. Dan zal deze oplossing werken voor alle datums. Succes verder!

[Pindakaas]

HvdLaan schreef op zondag 16 maart 2025 @ 15:38:
Dat scheelt inderdaad een behoorlijk wat stappen:)
Klopt dit zo een beetje?

[Afbeelding]

Yes inderdaad wat ik bedoelde. Goed bezig

[Lustucru]

Mocht je nu toch verder willen met het automatiseren van je maandelijkse werk, onderstaand stukje code doet in feite hetzelfde: het pakt wat delen uit de tekst en maakt er een datum van. Als je dit plakt in je code in plaats van die search en replace (. door -) werkt ook je macro weer.

Visual Basic:

Dim c As Range
On Error GoTo skipthisone                                                                   ' nodig om van tekst die niet te converteren is af te blijven
For Each c In Range(Cells(1, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1))    'doorloop alle gebruikte cellen in kolom A
    c.Value = DateSerial(Val(Mid(c.Text, 7, 4)), Val(Mid(c.Text, 4, 2)), Val(Left(c.Text, 2))) + TimeValue(Mid(c.Text, 12, 5))    ' doet het feitelijke werk
skipthisone:
Next c
Range("A:A").NumberFormat = "dd/mm/yyyy hh:mm;@"  ' zet het datumformaat goed

[ Voor 7% gewijzigd door Lustucru op 16-03-2025 19:37 ]