Tijden tussen twee tijden bepalen

[loetjeboetje]

Beste mensen,

Voor mijn studie ben ik bezig met een onderzoek naar de storingen van NS met een enorme datasheet, maar nu loop ik helaas ergens vast.
Elke storing heeft een bepaalde starttijd en een bepaalde eindtijd. Bijvoorbeeld:

Traject (NS) Traject (RDT) Oorzaak Start Einde Duur
Den Haag HS-Rotterdam C. Den Haag HS - Rotterdam Centraal Aanrijding met een persoon 1-1-2012 08:02 1-1-2012 10:14 132

Deze storing begint dus om 08:02 en eindigt om 10:14. Nu wil ik graag onderzoeken of het uur van de dag invloed heeft op het aantal storingen dat bezig is. Nu wil ik dus eigenlijk van elke storing weten op welke uren deze storing bezig is. Dus bij de storing van het voorbeeld is dat dan: 08:00, 09:00 en 10:00. Maar duurt een storing bijvoorbeeld van 14:05 tot 19:30 dan is het dus 14:00, 15:00, 16:00, 17:00, 18:00 en 19:00. Het uiteindelijke doel is om per uur het aantal storingen dat bezig is te verkrijgen zodat ik dus onder andere een grafiek kan maken die het aantal storingen dat per uur bezig is weergeeft (zie bijlage voor een voorbeeld).

Helaas heb ik geen idee hoe ik dit in Excel voor elkaar zou kunnen krijgen. Ik heb een klein voorbeeld bestandje toegevoegd met een aantal datapunten. Ik hoop dat een van jullie mij misschien verder kan helpen

Alvast heel erg bedankt!

 

[popipipo]

Bekijk deze oplossing op Blad2 eens

 

[oeldere]

een mogelijk begin.

tekst naar kolommen van kolom D naar kolom N

Daarna in kolom P =UUR(O2)

zie bijgevoegd bestand.

 

[Timshel]

@popipipo.
Naar zoiets heb ik ook gekeken, maar dit gaat niet goed op bijvoorbeeld rij 134 waarbij een storing voor middernacht begint en na middernacht eindigt.

 

[popipipo]

@Timshel
Daar had ik niet naar gekeken.
Ik had nog wel naar een draaitabel gekeken maar daar kwam ik niet uit.

 

[HSV]

Zoiets misschien?

 

[snb]

Dan zul je de tabel eerst moeten 'normaliseren' (zoals dat in database-termen heet).

Sub M_snb()
  sn = Sheet1.Cells(1).CurrentRegion
   
  For j = 2 To UBound(sn)
    For jj = 1 To DateDiff("h", sn(j, 4), sn(j, 5))
      Sheets("snb").Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, 4) = Array(sn(j, 1), sn(j, 2), sn(j, 3), DateAdd("h", jj, DateAdd("n", -Minute(sn(j, 4)), sn(j, 4))))
    Next
  Next     
End Sub

 

[loetjeboetje]

Bedankt allemaal voor de reacties!

@HSV jouw manier lijkt te werken, maar er gaan toch nog wat dingen mis volgens mij. Bijvoorbeeld deze:
Arnhem-Winterswijk Arnhem - Winterswijk Overwegstoring 3-1-2012 11:16 3-1-2012 12:04
Hier komt alleen 11:00 te staan in het blauwe deel, maar hier zou dus eigenlijk ook 12:00 moeten staan. Dit lijkt soms goed te gaan en soms fout. Weet je misschien hoe dit komt?

@snb Dat ziet er erg fancy uit en lijkt ook te werken, maar ook hier lijkt er bijvoorbeeld bij de eerste iets niet helemaal goed te gaan:
's-Hertogenbosch-Utrecht C. s-Hertogenbosch - Utrecht Centraal Aanrijding 1-1-2012 02:20 1-1-2012 03:04
Hier komt er alleen 03:00 te staan terwijl er ook 02:00 zou moeten komen te staan. Ik zie dat dit ook vaker gebeurt. Is hier een oplossing voor?

Alvast bedankt en de rest ook heel erg bedankt voor het meedenken!

 

[HSV]

Had ik niet bemerkt.
Het komt doordat de minuten in kolom E kleiner zijn dan de minuten in kolom D.
Hiermee moet het euvel zijn verholpen.

In N2 en doorvoeren naar rechts en naar beneden.

=ALS(AFRONDEN.N.VEELVOUD($E2-$D2;1/24)>=(KOLOM()-14)/24;AFRONDEN.N.VEELVOUD($D2+(KOLOM()-14)/24;1/24);"")

 

[snb]

@snb Dat ziet er erg fancy uit en lijkt ook te werken, maar ook hier lijkt er bijvoorbeeld bij de eerste iets niet helemaal goed te gaan:
's-Hertogenbosch-Utrecht C. s-Hertogenbosch - Utrecht Centraal Aanrijding 1-1-2012 02:20 1-1-2012 03:04
Hier komt er alleen 03:00 te staan terwijl er ook 02:00 zou moeten komen te staan. Ik zie dat dit ook vaker gebeurt. Is hier een oplossing voor?

Is natuurlijk wel een beetje vreemd dat als een storing om 8.59 begint het tijdstip 8.00 geïntroduceerd wordt als een tijdstip waarop die storing plaatsvond.
Is overigens uiterst simpel te 'verhelpen' met:

Sub M_snb()
  sn = Sheet1.Cells(1).CurrentRegion
   
  For j = 2 To UBound(sn)
    For jj = 0 To DateDiff("h", sn(j, 4), sn(j, 5))
      Sheets("snb").Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, 4) = Array(sn(j, 1), sn(j, 2), sn(j, 3), DateAdd("h", jj, DateAdd("n", -Minute(sn(j, 4)), sn(j, 4))))
    Next
  Next
End Sub

 

[sylvester-ponte]

hier een poging met matrixformule : dus afsluiten met ctrl shift enter

hij is denk ik nog best te vereenvoudigen

en als een storing meerdere dagen duurt, telt hij die dagen ook mee.

 

[loetjeboetje]

Bedankt allemaal voor de reacties. Ik heb uiteindelijk de oplossing van @snb gebruikt. Super dat ik weer verder kan met mijn onderzoek!