So konvertieren Sie Timestrings in Sekunden

Question

Ich habe eine CSV-Datei mit etwa 5.000 Zeilen in Excel importiert und mein Problem besteht darin, dass ich mehrere Zeitzeichenfolgen habe 1h1m1sund diese in Sekunden umwandeln muss.

Hier sind einige Beispiele:

Daten in Spalte A

0m11s         
2m32s        
3m10s        
1h2m35s

Ergebnis in Spalte B

11 seconds
152 seconds  
190 seconds    
3755 seconds

Ich habe versucht, Zellen zu formatieren, aber das Ergebnis war merkwürdig!

Ist dies möglich und wenn ja, wie kann ich die Zeitzeichenfolge in Sekunden umwandeln?

Answer 1

Dies ist mit VBA relativ einfach.

Eine Möglichkeit besteht darin, reguläre Ausdrücke zum Parsen der Zeichenfolge zu verwenden und dann jeden Abschnitt mit der entsprechenden Konvertierung zu multiplizieren.

Um diese benutzerdefinierte Funktion (UDF) einzugeben, alt-F11öffnen Sie den Visual Basic-Editor. Stellen Sie sicher, dass Ihr Projekt im Fenster „Projekt-Explorer“ hervorgehoben ist. Wählen Sie dann im oberen Menü Insert/Moduleden folgenden Code aus und fügen Sie ihn in das sich öffnende Fenster ein.

Um diese benutzerdefinierte Funktion (UDF) zu verwenden, geben Sie eine Formel wie folgt ein:

=convSeconds(A1)

in irgendeiner Zelle.

Dies gibt nur die Anzahl der Sekunden zurück, wie unten gezeigt. Wenn Sie das Wort anhängen möchten Seconds, können Sie entweder die Formel mit der Zeichenfolge verketten oder eine benutzerdefinierte Zahlenformatierung verwenden (wobei die Zahlenqualität des Ergebnisses erhalten bleibt).

Option Explicit
Function convSeconds(s As String) As Long
    Dim RE As Object, MC As Object
    Dim SEC As Long
Set RE = CreateObject("vbscript.regexp")
With RE
    .Global = True
    .ignorecase = True
    .Pattern = "(?:(\d+)h)?(?:(\d+)m)?(?:(\d+)s)?"
    If .test(s) = True Then
        Set MC = .Execute(s)
        With MC(0)
            SEC = SEC + .submatches(0) * 3600 'hours
            SEC = SEC + .submatches(1) * 60   'minutes
            SEC = SEC + .submatches(2)        'seconds
        End With
    End If
End With
convSeconds = SEC
End Function

Hier ist eine Erklärung des regulären Ausdrucks

Extrahieren h/m/s

(?:(\d+)h)?(?:(\d+)m)?(?:(\d+)s)?

• Passen Sie den regulären Ausdruck unten an (?:(\d+)h)?
  • Zwischen null und einem Mal, so oft wie möglich, Rückgabe nach Bedarf (gierig) ?
  • Ordnen Sie den unten stehenden regulären Ausdruck zu und erfassen Sie seine Übereinstimmung in der Rückreferenznummer 1. (\d+)
    • Übereinstimmung mit einem einzelnen Zeichen, das eine „Ziffer“ ist \d+
      • Zwischen einem und unbegrenzt oft, so oft wie möglich, Rückgabe nach Bedarf (gierig) +
  • Das Zeichen „h“ muss wörtlich übereinstimmen h
• Passen Sie den regulären Ausdruck unten an (?:(\d+)m)?
  • Zwischen null und einem Mal, so oft wie möglich, Rückgabe nach Bedarf (gierig) ?
  • Ordnen Sie den unten stehenden regulären Ausdruck zu und erfassen Sie seine Übereinstimmung in der Rückreferenznummer 2. (\d+)
    • Übereinstimmung mit einem einzelnen Zeichen, das eine „Ziffer“ ist \d+
      • Zwischen einem und unbegrenzt oft, so oft wie möglich, Rückgabe nach Bedarf (gierig) +
  • Passen Sie den Buchstaben „m“ wörtlich an m
• Passen Sie den regulären Ausdruck unten an (?:(\d+)s)?
  • Zwischen null und einem Mal, so oft wie möglich, Rückgabe nach Bedarf (gierig) ?
  • Ordnen Sie den unten stehenden regulären Ausdruck zu und erfassen Sie seine Übereinstimmung in der Rückreferenznummer 3. (\d+)
    • Übereinstimmung mit einem einzelnen Zeichen, das eine „Ziffer“ ist \d+
      • Zwischen einem und unbegrenzt oft, so oft wie möglich, Rückgabe nach Bedarf (gierig) +
  • Ordnen Sie das Zeichen „s“ wörtlich zu s

Hergestellt mitRegexBuddy

Answer 2

Hier ist eine einfache Möglichkeit, dies zu tun. Es könnte alles in einer Formel erledigt werden, aber ich habe einige Hilfsspalten verwendet, um Wiederholungen zu vermeiden.

In deinen Beispielen gibt es immer mindestens Platzhalter für Minuten und Sekunden, aber Stunden werden nur dann berücksichtigt, wenn es Stunden gibt. Der erste Schritt sucht nach einem "h". Formel in B1:

=FIND("h",A1)

Wenn kein „h“ vorhanden ist, wird ein Fehler zurückgegeben. Andernfalls wird die Position von h in der Zeichenfolge zurückgegeben (vorausgesetzt, der Stundenwert könnte 9 überschreiten, andernfalls könnten Sie einfach das linke Zeichen nehmen).

Spalte C entfernt den Teil, der immer gleich bleibt. Die Formel in C1:

=IF(ISERROR(B1),A1,MID(A1,B1+1,LEN(A1)))

Wenn kein „h“ gefunden wurde, wird der Originalstring verwendet, andernfalls alles nach dem h.

In Spalte D ist das „m“ zu finden. Die Formel in D1 ist ähnlich der in B1:

=FIND("m",C1)

Spalte E verwendet die Stücke, um die Sekunden zu berechnen. Die Formel in E1:

=IF(ISERROR(B1),0,3600*LEFT(A1,B1-1))+LEFT(C1,D1-1)*60+MID(C1,D1+1,LEN(C1)-D1-1)

Wenn kein „h“ gefunden wurde, ist der Stundenbeitrag gleich Null, andernfalls beträgt er das 3600-fache dessen, was links vom „h“ gefunden wurde. Der Minutenbeitrag beträgt das 60-fache dessen, was links vom „m“ gefunden wurde. Der Sekundenbeitrag ist die Zahl, die nach dem „m“ und vor dem letzten Buchstaben (dem „s“) gefunden wurde.

Sie können die Spalten B:D ausblenden. Wenn Sie wirklich alles in einer einzigen Formel haben möchten, ersetzen Sie einfach die Verweise auf die Hilfszellen durch die zugehörige Hilfszellenformel.

Vorbehalt: Diese Formel befasst sich mit den Eigenschaften der Daten in der Frage, die immer Minuten- und Sekundenwerte enthalten. Für eine Situation, in der Minuten und/oder Sekunden nicht unbedingt enthalten sind, wäre eine kompliziertere Formel erforderlich.

Answer 3

Ich gehe davon aus, dass sich die Eingabedaten in Spalte A, Zeile 1 befinden.

Sie können es mit der folgenden Formel versuchen (kopieren + in B1 einfügen und bei Bedarf nach unten ziehen):

=(IF(ISERROR(FIND("h",A1)),0,INT(MID(A1,1,FIND("h",A1)-1)))*3600)+(INT(IF(ISERROR(FIND("h",A1)),LEFT(A1,FIND("m",A1)-1),MID(A1,IF(ISERROR(FIND("h",A1)),0,FIND("h",A1)+1),FIND("m",A1)-FIND("h",A1)-1)))*60)+INT(MID(A1,FIND("m",A1)+1,FIND("s",A1)-FIND("m",A1)-1))

Answer 4

Auf diese Frage gibt es bereits Antworten, die sich mit dem Problem in der Frage befassen, das die Vereinfachung enthält, dass Minuten und Sekunden immer enthalten sind. Die Leute landen hier möglicherweise mit dem ähnlichen, aber allgemeineren Problem, dass nur die benötigten Zeitkomponenten enthalten sind, sodass eine oder mehrere Komponenten fehlen können.

Ron Rosenfelds großartige Antwort behandelt das bereits. Für Leser, die mit VBA oder Regex nicht vertraut sind und möglicherweise eine formelbasierte Lösung bevorzugen, die sie leichter anpassen und warten können, behandeln die anderen aktuellen Antworten den allgemeinen Fall nicht. Ich werde diese formelbasierte Lösung als separate Antwort veröffentlichen, da sie für den begrenzten Fall in dieser Frage komplizierter ist als erforderlich, und die Kombination mit meiner anderen Antwort würde beide Lösungen weniger zugänglich machen.

Diese Lösung verwendet ebenfalls einige Hilfsspalten, um Formelwiederholungen zu vermeiden, verfolgt jedoch einen etwas anderen Ansatz. Die Spalten B bis D suchen jeweils nach den unterschiedlichen Buchstaben der Zeitkomponente. Die Formeln in der ersten Zeile:

B1:  =FIND("h",A1)
C1:  =FIND("m",A1)
D1:  =FIND("s",A1)

Wenn der Buchstabe vorhanden ist, wird die Position dieses Buchstabens in der Originalzeichenfolge zurückgegeben, andernfalls tritt ein Fehler auf. Das „s“ ist, falls vorhanden, immer das letzte Zeichen, aber die Ermittlung seiner Zeichenposition vereinfacht das Extrahieren des Sekundenwerts, und FIND dient dem doppelten Zweck, festzustellen, ob es vorhanden ist.

Spalte E extrahiert jeden Zeitkomponentenwert, multipliziert ihn mit dem Umrechnungsfaktor für Sekunden und addiert sie. Sie verwendet MID, um die Werte basierend auf den Markierungspositionen in den Spalten B bis D zu extrahieren.

Die Komplexität liegt darin, dass bei Minuten und Sekunden einige oder alle der vorhergehenden Zeitkomponenten fehlen können. Die Formel muss bestimmen, wo in der Zeichenfolge die aktuelle Komponente beginnt, basierend darauf, welche der vorhergehenden Komponenten vorhanden sind. Die Formel in E1 lautet:

=3600*IFERROR(LEFT(A1,B1-1),0)+
 60*IFERROR(MID(A1,1+IFERROR(B1,0),C1-1-IFERROR(B1,0)),0)+
 IFERROR(MID(A1,1+MAX(IFERROR(B1,0),IFERROR(C1,0)),D1-1-MAX(IFERROR(B1,0),IFERROR(C1,0))),0)

Zur besseren Lesbarkeit habe ich der Formel zwischen den Zeitkomponenten Zeilenumbrüche hinzugefügt. Entfernen Sie diese also, bevor Sie die Formel kopieren und einfügen.

Hours ist ziemlich einfach. Es nimmt die Zeichen links vor dem „h“ oder Null, wenn „h“ fehlt, und multipliziert sie mit 3600.

Dem Minutenwert können nur Stunden vorangestellt werden. Wenn ein Minutenwert vorhanden ist, berechnet es den Startpunkt und die Länge für MID basierend darauf, ob eine Stundenkomponente vorhanden ist.

Die Position des Sekundenwerts, falls vorhanden, hängt vom Vorhandensein einer oder beider Stunden- und Minutenkomponenten ab. Die Position des „m“, falls vorhanden, liegt in der Zeichenfolge immer weiter hinten als die Position des „h“, falls vorhanden. Wenn eine der Komponenten nicht vorhanden ist, verwendet die Formel für diese Komponente den Wert Null. Die MAX-Funktion liefert dann die am weitesten entfernte Position in der ursprünglichen Zeichenfolge, die dem Sekundenwert vorangeht, und bestimmt, wie viel der Zeichenfolgenlänge von vorherigen Zeitkomponenten eingenommen wird.

Die Hilfsspalten können ausgeblendet werden. Sie könnten in die Formel in Spalte E integriert werden, aber wenn man bedenkt, wie oft diese Werte verwendet werden, wäre die resultierende Formel wahnsinnig lang und schwer zu pflegen.