Clever-Excel-Forum

Seiten: 1 2

Hallo Connor,

per Makro z.B. so:

Sub TextzeitenZuSekunden()

 Dim lngZ As Long

 Dim varQ As Variant

 varQ = Range("A2", Cells(Rows.Count, 1).End(xlUp)).Value

 With Application.WorksheetFunction

   For lngZ = 1 To Ubound(varQ)

     varQ(lngZ, 1) = Evaluate(.Substitute(.Substitute(.Substitute(.Substitute(varQ(lngZ, 1), " seconds", "*1"), " Minutes", "*60+"), " Hours", "*3600+"), " Days", "*86400+"))

   Next lngZ

 End With

 Range("B2").Resize(Ubound(varQ)).Value = varQ

End Sub

Die Ausgabe erfolgt in Spalte B in Sekunden.

Gruß Uwe

Moin Connor,

als UDF (Funktion) bietet sich dieses an:

Code:
Option Explicit

Function Text2Time(c As Range) As Date

   Dim D As Integer, H As Integer, M As Integer, S As Integer

   Dim PosD As Integer, PosH As Integer, PosM As Integer, PosS As Integer

   Dim cSplit

   cSplit = Split(c, " ")

   Select Case UBound(cSplit)

   Case 7

      D = cSplit(0)

      H = cSplit(2)

      M = cSplit(4)

      S = cSplit(6)

   Case 5

      H = cSplit(0)

      M = cSplit(2)

      S = cSplit(4)

   Case 3

      M = cSplit(0)

      S = cSplit(2)

   Case Else

      S = cSplit(0)

   End Select

   Text2Time = TimeSerial(D * 24 + H, M, S)

End Function

Die Ausgabe erfolgt als Zeit, das Zahlenformat sollte [hh]:mm:ss sein.

Danke an alle für die tollen Lösungen "made my day!"

Seiten: 1 2

Kuwer

GMG-CC

Connor