AW: von bis Datum in Jahre, Mon, Woch, Tage
bis
Hallo Ruediger,
ich hab' mir deine Mappe nicht im Detail angeschaut und kann daher zu deinen Formeln
nichts aussagen. Aber falls du eine VBA-Funktion suchst, die vergleichbares liefert,
dann hab' ich vielleicht was für dich ...
Function ZeitDifferenz(Von As Date, Bis As Date, Typ As Long) As String
Dim dblWert As Double
Dim intWert As Long
Dim Start As Date
Dim Ende As Date
Dim Jahre As Long
Dim Monate As Long
Dim Wochen As Long
Dim Tage As Long
Dim Tage2 As Long
Dim Zeit As Date
Dim Stunden As Long
Dim Minuten As Long
Dim Sekunden As Long
Dim Txt As String
Application.Volatile
If Von
Start = CDate(Int(Von))
Ende = CDate(Int(Bis))
dblWert = CDbl(Bis) - CDbl(Von)
dblWert = WorksheetFunction.Round(dblWert, 8)
intWert = Int(dblWert)
Zeit = CDate(dblWert - intWert)
If intWert
Ende = Ende - 1
End If
If Month(Ende) > Month(Start) Then
Jahre = Year(Ende) - Year(Start)
ElseIf Month(Ende)
Jahre = Year(Ende) - Year(Start) - 1
ElseIf Day(Ende) >= Day(Start) Then
Jahre = Year(Ende) - Year(Start)
ElseIf Day(Ende)
Jahre = Year(Ende) - Year(Start) - 1
End If
If Month(Ende) > Month(Start) And Day(Ende) >= Day(Start) Then
Monate = Month(Ende) - Month(Start)
ElseIf Month(Ende) > Month(Start) And Day(Ende)
Monate = Month(Ende) - Month(Start) - 1
ElseIf Month(Ende) = Day(Start) Then
Monate = (12 + Month(Ende)) - Month(Start)
ElseIf Month(Ende)
Monate = (12 + Month(Ende)) - Month(Start) - 1
ElseIf Month(Ende) = Month(Start) And Day(Ende) >= Day(Start) Then
Monate = 0
ElseIf Month(Ende) = Month(Start) And Day(Ende)
Monate = 11
End If
If Day(Ende) >= Day(Start) Then
Tage = Day(Ende) - Day(Start)
ElseIf Day(Ende)
Tage = TageDesMonats(Month(Start), Year(Start)) - Day(Start) + Day(Ende)
End If
Wochen = Tage \ 7
Tage2 = Tage - (Wochen * 7)
Stunden = Hour(Zeit)
Minuten = Minute(Zeit)
Sekunden = Second(Zeit)
End If
If Typ = 0 Then
Txt = Jahre & "'J " & Monate & "'M " & Tage & "'T " & _
Format(Zeit, "hh:mm:ss") & "*"
ElseIf Typ = 1 Then
If Jahre = 1 Then
Txt = "1 Jahr,"
Else
Txt = Jahre & " Jahre,"
End If
If Monate = 1 Then
Txt = Txt & " 1 Monat,"
Else
Txt = Txt & " " & Monate & " Monate,"
End If
If Tage = 1 Then
Txt = Txt & " 1 Tag,"
Else
Txt = Txt & " " & Tage & " Tage,"
End If
If Stunden = 1 Then
Txt = Txt & " 1 Stunde,"
Else
Txt = Txt & " " & Stunden & " Stunden,"
End If
If Minuten = 1 Then
Txt = Txt & " 1 Minute,"
Else
Txt = Txt & " " & Minuten & " Minuten,"
End If
If Sekunden = 1 Then
Txt = Txt & " 1 Sekunde,"
Else
Txt = Txt & " " & Sekunden & " Sekunden,"
End If
ElseIf Typ = 2 Then
If Jahre = 1 Then
Txt = "1 Jahr,"
ElseIf Jahre > 1 Then
Txt = Jahre & " Jahre,"
End If
If Monate = 1 Then
Txt = Txt & " 1 Monat,"
ElseIf Monate > 1 Then
Txt = Txt & " " & Monate & " Monate,"
End If
If Tage = 1 Then
Txt = Txt & " 1 Tag,"
ElseIf Tage > 1 Then
Txt = Txt & " " & Tage & " Tage,"
End If
If Stunden = 1 Then
Txt = Txt & " 1 Stunde,"
ElseIf Stunden > 1 Then
Txt = Txt & " " & Stunden & " Stunden,"
End If
If Minuten = 1 Then
Txt = Txt & " 1 Minute,"
ElseIf Minuten > 1 Then
Txt = Txt & " " & Minuten & " Minuten,"
End If
If Sekunden = 1 Then
Txt = Txt & " 1 Sekunde,"
ElseIf Sekunden > 1 Then
Txt = Txt & " " & Sekunden & " Sekunden,"
End If
ElseIf Typ = 3 Then
If Jahre = 1 Then
Txt = "1 Jahr,"
Else
Txt = Jahre & " Jahre,"
End If
If Monate = 1 Then
Txt = Txt & " 1 Monat,"
Else
Txt = Txt & " " & Monate & " Monate,"
End If
If Wochen = 1 Then
Txt = Txt & " 1 Woche,"
Else
Txt = Txt & " " & Wochen & " Wochen,"
End If
If Tage2 = 1 Then
Txt = Txt & " 1 Tag,"
Else
Txt = Txt & " " & Tage2 & " Tage,"
End If
If Stunden = 1 Then
Txt = Txt & " 1 Stunde,"
Else
Txt = Txt & " " & Stunden & " Stunden,"
End If
If Minuten = 1 Then
Txt = Txt & " 1 Minute,"
Else
Txt = Txt & " " & Minuten & " Minuten,"
End If
If Sekunden = 1 Then
Txt = Txt & " 1 Sekunde,"
Else
Txt = Txt & " " & Sekunden & " Sekunden,"
End If
Else
If Jahre = 1 Then
Txt = "1 Jahr,"
ElseIf Jahre > 1 Then
Txt = Jahre & " Jahre,"
End If
If Monate = 1 Then
Txt = Txt & " 1 Monat,"
ElseIf Monate > 1 Then
Txt = Txt & " " & Monate & " Monate,"
End If
If Wochen = 1 Then
Txt = Txt & " 1 Woche,"
ElseIf Wochen > 1 Then
Txt = Txt & " " & Wochen & " Wochen,"
End If
If Tage2 = 1 Then
Txt = Txt & " 1 Tag,"
ElseIf Tage2 > 1 Then
Txt = Txt & " " & Tage2 & " Tage,"
End If
If Stunden = 1 Then
Txt = Txt & " 1 Stunde,"
ElseIf Stunden > 1 Then
Txt = Txt & " " & Stunden & " Stunden,"
End If
If Minuten = 1 Then
Txt = Txt & " 1 Minute,"
ElseIf Minuten > 1 Then
Txt = Txt & " " & Minuten & " Minuten,"
End If
If Sekunden = 1 Then
Txt = Txt & " 1 Sekunde,"
ElseIf Sekunden > 1 Then
Txt = Txt & " " & Sekunden & " Sekunden,"
End If
End If
If Len(Txt) > 0 Then
Txt = Left(Txt, Len(Txt) - 1)
Txt = Trim(Txt)
End If
ZeitDifferenz = Txt
End Function
Private Function TageDesMonats(Monat As Long, Jahr As Long) As Long
Dim Tage As Long
Select Case Monat
Case 1, 3, 5, 7, 8, 10, 12
Tage = 31
Case 4, 6, 9, 11
Tage = 30
Case 2
If IstSchaltjahr(Jahr) Then
Tage = 29
Else
Tage = 28
End If
End Select
TageDesMonats = Tage
End Function
Private Function IstSchaltjahr(Jahr As Long) As Boolean
If (Jahr Mod 4 = 0 And Jahr Mod 100 <> 0) Or _
(Jahr Mod 400 = 0) Then IstSchaltjahr = True
End Function
Für die Ausgabe gibt's die Typen 0 bis 4!
Gruß
Sigi