Wisselende tijdnotities uniform maken

[NickBlanc]

Hallo allemaal,

ik zit met het volgende vraagstuk. Ik krijg een rapportage waarbij de notitie van de tijd steeds anders is, waardoor ik nu handwerk heb. Ik krijg 3 cellen aangeleverd (x120 rijen maar dat maakt niet uit voor het principe) met tijd: Cel A1 is ":40", Cel B1 is "01:55" en Cel C1 is "01:55:21".

Nu wil ik dat de waarden van deze cellen bij elkaar worden opgeteld en dat de uitkomst wordt uitgedrukt in aantal seconden in cel D1 (in dit voorbeeld . Dat laatste is geen probleem door de uitkomst van een xx:xx:xx cel te vermenigvuldigen met 86400. In dit voorbeeld zou dat dus 7076 moeten worden). Maar ik zit nu steeds handmatig de cellen die NIET bestaan uit xx:xx:xx zelf aan te vullen, omdat alles wat korter is door Excel niet herkend wordt als tijd of als de verkeerde tijd (Bijvoorbeeld: A1 minuten:seconden + B1 uren:minuten:seconden = Excel denk dat de minuten: seconden in A1

Ik zat al te kijken naar functies als Herhaling en Lengte, maar dan kreeg ik niet het gewenste resultaat. Weet iemand een formule/manier om dit netjes te doen? Uiteindelijk wil ik er ook een gewogen gemiddelde van maken. Zie bijlage voor wat ik bedoel.

Bij voorbaat dank (sorry dat het misschien wat onduidelijk is, maar de bijlage verklaart veel hopelijk).

 

[MarcelBeug]

Als ik het goed begrijp, moet je de cellen met getallen (tijden) gewoon optellen en de cellen met tekst eerst herleiden tot een tijd.
Maar dan moet je wel eenduidig weten wat dat voorstelt.
Klopt het dat je zo maximaal mogelijk moet gaan zitten ":40" is 40 minuten, enzovoorts?

 

[Ginger]

Dwing via een celvalidatie bij de gebruiker af dat altijd het tijdformat wordt gebruikt. Hiermee voorkom je dat je achteraf zaken moet gaan herstellen.

 

[NickBlanc]

Correct en bijna correct Marcel.

Ik moet de cellen met tijd optellen. Helaas is niet elke cel gelijk. De cellen met waardes die langer zijn dan 1 uur worden correct genoteerd: dus bijv. 01:01:01 (1 uur, 1 minuut en 1 seconde). Helaas ziet excel mijn aangeleverde data (komt uit een rapportage programma op mijn werk) niet altijd zo. 46 minuten en 3 seconden ziet hij als :46:03 en vervolgens inderdaad tekst. Hetzelfde geldt voor cellen met :03. Dat zijn 3 seconden maar dat ziet excel ook als tekst.

Ik heb geprobeerd simpelweg de celeigenschappen te wijzigen (dan zou ik klaar zijn geweest), maar dat pakt Excel ook niet (anders had ik dit topic niet geopend ).

 

[NickBlanc]

Dwing via een celvalidatie bij de gebruiker af dat altijd het tijdformat wordt gebruikt. Hiermee voorkom je dat je achteraf zaken moet gaan herstellen.

Dat is een wens, maar helaas niet meer dan een wens. De data wordt uitgedraaid uit een log dataprogramma en de export hieruit laat op z'n zachtst gezegd te wensen over. Evenals de mogelijkheid om hier eisen over te stellen. Ik ben het met je eens dat dit alles voorkomen kan worden door de bron aan te pakken, maar dat is helaas geen optie.

 

[Ginger]

Wat een bijzondere rapportagetool is dat, die een tijdveld zo "verkracht". Vind ik persoonlijk héél onlogisch. Hoe is de notatie in die rapportagetool dan? Wat moet de gebruiker dáár opgeven?
Hoe ziet je interface documentje er vervolgens uit die je vanuit die tool krijgt om in te lezen in Excel?

[EDIT] was mijn post aan 't maken en zag pas na het posten de reactie hierboven... Het antwoord op mijn vraag was dus al gegeven voordat ik mijn vraag had gepost. Toch wel knap...

 

[alphamax]

met 1 : zijn seconden
met 2 : zijn minuten en seconden
met 3 : zijn uren, minuten en seconden

Maar het beste is terug te gaan naar het orginele export bestand.
Excel heeft soms de vervelende eigenschap gegevens naar goeddunken aan te passen tijdens kopieeren en plakken.

 

[Ginger]

Ik zit je bestandje ff te bekijken... HOE importeer jij die gegevens? Gaat dat via de TextToColumns (tekst naar kolommen) Wizard? Zorg er in dat geval voor dat Excel al die geïmporteerde kolommen met "tijd" als tekst ziet en vooral NIET als tijd. Je 5 minuten en 30 seconden worden nu gezien als 5 uur en 30 minuten. Da's jammer voor een verdere verwerking.
Als je de mogelijkheid hebt om alles als tekst te importeren, kan er daarna met een macrootje overheen worden gegaan om al die cellen om te zetten naar een "echte" tijdwaarde. Vanaf dat moment kan je alles in je sheet berekenen.

 

[Ginger]

met 1 : zijn seconden
met 2 : zijn minuten en seconden
met 3 : zijn uren, minuten en seconden

@Alphamax, je stelling gaat op bij de waardes waar de interpreter van Excel niets mee kon. Het is mij namelijk een raadsel waarom de waardes van kolom D wél naar uren + minuten zijn gezet en die van kolom G bijvoorbeeld niet. Ofwel, wáárom staat bij de ene minuten + secondes wél een dubbele punt voor de minuten en bij de andere niet???

 

[NickBlanc]

Terechte vraag. Ik krijg een bestand wat er uit ziet als de bijlage. In het programma heb ik een 'export to excel' functie, maar dan begint de ellende zoals in de excel bijlage uit de startpost blijkt.

 

[NickBlanc]

@Alphamax, je stelling gaat op bij de waardes waar de interpreter van Excel niets mee kon. Het is mij namelijk een raadsel waarom de waardes van kolom D wél naar uren + minuten zijn gezet en die van kolom G bijvoorbeeld niet. Ofwel, wáárom staat bij de ene minuten + secondes wél een dubbele punt voor de minuten en bij de andere niet???

Dát is inderdaad het raadsel, maar dat ligt helaas opgesloten in de applicatie en niet wijzigbaar. Dus aan mij de schone taak om er iets werkbaars van de brouwen.

 

[MarcelBeug]

Op basis van het voorbeeldbestand kom ik op de volgende formule in J2 en gekopieerd t/m M3:

=ALS(ISGETAL(D2);D2;--RECHTS("00:00"&D2;8))

Het lijkt me handig om de berekende velden apart te houden van de input (en deze niet te overschrijven). Zo houd je visuele controle.

Edit: het doelsysteem lijkt mij ook wat merkwaardig, als je de tijd in seconden wilt hebben.
Of ben je wellicht niet bekend met tijdformaat :mm:ss waarmee je voorbij 24 uur kunt rekenen/weergeven?

 

[NickBlanc]

Op basis van het voorbeeldbestand kom ik op de volgende formule in J2 en gekopieerd t/m M3:

=ALS(ISGETAL(D2);D2;--RECHTS("00:00"&D2;8))

Het lijkt me handig om de berekende velden apart te houden van de input (en deze niet te overschrijven). Zo houd je visuele controle.

Edit: het doelsysteem lijkt mij ook wat merkwaardig, als je de tijd in seconden wilt hebben.
Of ben je wellicht niet bekend met tijdformaat :mm:ss waarmee je voorbij 24 uur kunt rekenen/weergeven?

Thanks, dit helpt al een heel stuk. Alleen pakt deze formule de tijd uit kolom D Tijd 1 (minuten:seconden) nu als uren:minuten.

Het doel is in principe om te rapporteren hoe lang iemand over een bepaalde taak doet. We rapporteren dat in seconden. Dus we krijgen aangeleverd in tijd uu:mm:ss en willen uiteindelijk alleen ss hebben als getal.

Edit: Even versimpeld uitgelegd a.d.h.v. het Excel bestand. Jantje doet op maandag 18 taken, elke taak bestaat uit 4 onderdelen. Onderdeel 1 duurt een paar minuten, onderdeel 2 meestal een paar seconden maar soms ook een paar minuten, onderdeel 3 een paar uur en onderdeel 4 weer een paar minuten. We krijgen de totaaltijd per onderdeel en het aantal taken. Dan wil ik weten hoe lang Jantje gemiddeld over 1 taak doet. Ik hoop voor jantje dat dat niet langer dan 24 uur is, anders gaan we afscheid van hem nemen.

 

[alphamax]

Zijn er meer export mogelijkheden zoals csv of xml? en welke applicatie is het dan?

 

[NickBlanc]

Zijn er meer export mogelijkheden zoals csv of xml?

Dat helaas niet. Wel kan ik de seporator tussen de waarden aanpassen. Ik heb de keuze tussen spatie, kolom, dubbele punt en puntkomma.

 

[alphamax]

Een export to excel functie waar je de separator kan kiezen, vreemd?
Of het wordt geexporteerd naar een .xls? bestand.
Of het wordt geexporteerd naar een bestand waar je de extensie kan kiezen en de separator kan kiezen.
Ik denk dat het probleem ontstaat zodra je het bestand opent met excel, in het orginele exportbestand zit het probleem niet

 

[Ginger]

Doe eens puntkomma... In elk geval géén dubbele punt! Met een beetje geluk ziet Excel het dan als csv....

 

[VenA]

Exporteer het bij voorkeur NIET als .csv. Als je het bestand opent via File Open dan herkent Excel dit als een 'Excelbestand' en zal dan de in dit geval vervelende standaard instellingen gaan gebruiken. Als je er een .txt of iets anders van maakt dan wordt er een wizzard gestart waarmee je de indeling zelf kan regelen. En dus, zoals door Ginger al aangegeven, alle kolommen als tekst gaat inlezen.

Met in dit geval een UDF is er dan wel weer wat bruikbaars van te maken.

Function VenA(r) As Double
For Each cl In r
    ar = Split(cl, ":")
    Select Case UBound(ar)
        Case 0
            VenA = VenA + TimeValue("0:0:" & ar(0))
        Case 1
            If ar(0) = "" Then VenA = VenA + TimeValue("0:0:" & ar(1)) Else VenA = VenA + TimeValue("0:" & ar(0) & ":" & ar(1))
        Case 2
            If ar(0) = "" Then VenA = VenA + TimeValue("0:" & ar(1) & ":" & ar(2)) Else VenA = VenA + TimeValue(cl)
    End Select
Next cl
VenA = VenA * 86400
End Function

 

[MarcelBeug]

Het lijkt inderdaad dat je met de oorspronkelijke aanpak er niet gaat komen: stel dat de 1:37:23 in F2 van het voorbeeldbestand nou eens precies 1:37:00 was geweest en Excel er (dus) 1:37 van had gemaakt. Dan kun je op geen enkele manier bepalen of het hier 1 uur 37 minuten is of 1 minuut 37 seconden.

Vooral verder gaan met de overige suggesties, zou ik zo zeggen.

 

[snb]

Sub M_snb()
    For j = 1 To 7
      MsgBox Format(Replace(Choose(j, Val(""), ":22", "1:45", "20:40", ":20:41", "3:09:09", "22:12:30"), ":", ""), "00:00:00")
      MsgBox CDate(Format(Replace(Choose(j, Val(""), ":22", "1:45", "20:40", ":20:41", "3:09:09", "22:12:30"), ":", ""), "00:00:00"))
    Next
End Sub

Met Format valt alles te standaardiseren, met CDate naar tijd te converteren.