VBA-Programmierung in Microsoft Excel

Tutorial: Excel-Beispiele

Dezimal- als Zeitformat und umgekehrt

Gruppe

DatumZeit

Bereich

Format

Thema

Dezimal- als Zeitformat und umgekehrt

Problem

Wie kann ich Dezimalzahlen in Uhrzeiten und Uhrzeiten in Dezimalzahlen umwandeln und addieren?

Lösung

Nur anhand einer Beipspielarbeitsmappe darstellbar.




StandardModule: Modul1

Sub LinkInfo()
   Dim arrDetails As Variant
   Dim rng As Range, rngSel As Range
   Dim iCounter As Integer, iRow As Integer, sText As String
   Set rngSel = Range("A1:E9")
   Workbooks.Add 1
   Range("A1").Value = "LinkAddress:"
   Range("B1").Value = "Path:"
   Range("C1").Value = "Workbook:"
   Range("D1").Value = "Worksheet:"
   Range("E1").Value = "Range:"
   Range("A1:E1").Font.Bold = True
   iRow = 1
   For Each rng In rngSel
      If rng.HasFormula Then
         If InStr(rng.Formula, "\[") Then
            arrDetails = GetDetails(rng.Formula)
            iRow = iRow + 1
            Cells(iRow, 1).Value = rng.Address
            For iCounter = 1 To 4
               Cells(iRow, iCounter + 1).Value = arrDetails(iCounter)
            Next iCounter
         End If
      End If
   Next rng
   Columns.AutoFit
End Sub

Private Function GetDetails(sTxt As String) As Variant
   Dim sWkb As String, sWks As String, sRng As String
   Dim sPath As String
   Dim arr(1 To 4) As String
   arr(1) = Mid(sTxt, InStr(sTxt, "'") + 1, _
      InStr(sTxt, "[") - InStr(sTxt, "'") - 2)
   arr(2) = Mid(sTxt, InStr(sTxt, "[") + 1, _
      InStr(sTxt, "]") - InStr(sTxt, "[") - 1)
   arr(3) = Mid(sTxt, InStr(sTxt, "]") + 1, _
      InStr(sTxt, "'!") - InStr(sTxt, "]") - 1)
   arr(4) = Right(sTxt, Len(sTxt) - InStr(sTxt, "!"))
   GetDetails = arr
End Function

    


Beiträge aus dem Excel-Forum zu den Themen DatumZeit und Format