Tage zwischen zwei Datums

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Sub Datum_von_bis_aufloesen() Dim wks As Worksheet Dim Zeile As Long, Zeile_L As Long Dim strDatum_von_bis As String, Datum1 As Date, Datum2 As Date, Datum As Date Set wks = ActiveSheet Application.ScreenUpdating = False With wks Zeile_L = .Cells(.Rows.Count, 1).End(xlUp).Row For Zeile = Zeile_L To 2 Step -1 '2 = 1. Zeile mit von-bis-Datum - ggf. anpassen strDatum_von_bis = .Cells(Zeile, 1).Text Datum1 = CDate(Trim(Left(strDatum_von_bis, InStr(1, strDatum_von_bis, "-") - 1))) Datum2 = CDate(Trim(Mid(strDatum_von_bis, InStr(1, strDatum_von_bis, "-") + 1))) For Datum = Datum2 To Datum1 + 1 Step -1 .Cells(Zeile + 1, 1).EntireRow.Insert .Cells(Zeile + 1, 1).Value = Datum Next .Cells(Zeile, 1).Value = Datum1 Next End With Application.ScreenUpdating = True End Sub

Sub datum_ergänzen() Dim LR As Long, i As Long, j As Long, Arr, Z As Long LR = Cells(Rows.Count, "A").End(xlUp).Row 'letzte Zeile der Spalte Columns("b").ClearContents For i = 1 To LR Arr = Split(Cells(i, 1), " - ") For j = CDate(Arr(0)) To CDate(Arr(1)) Z = Z + 1 Cells(Z, 2) = CDate(j) Next Next Columns("A").Delete xlLeft End Sub

Option Explicit Sub datum_ergänzen() Dim LR As Long, i As Long, j As Long, Arr, Z As Long LR = Cells(Rows.Count, "A").End(xlUp).Row 'letzte Zeile der Spalte Columns("B").ClearContents Z = 1 For i = 2 To LR If InStr(Cells(i, 1), " - ") = 0 Then Z = Z + 1 Cells(Z, 2) = CDate(Cells(i, 1)) Else Arr = Split(Cells(i, 1), " - ") For j = CDate(Arr(0)) To CDate(Arr(1)) Z = Z + 1 Cells(Z, 2) = CDate(j) Next End If Next Columns("A").Delete xlLeft End Sub

Hallo Simone,
ich hab mein Makro an deine Beispieldatei angepasst, so dass Zeilen, die keinen Bindestrich enthalten übersprungen werden.
Gruß
Franz

Sub Datum_von_bis_aufloesen()
Dim wks As Worksheet
Dim Zeile As Long, Zeile_L As Long
Dim strDatum_von_bis As String, Datum1 As Date, Datum2 As Date, Datum As Date
Set wks = ActiveSheet
Application.ScreenUpdating = False
With wks
Zeile_L = .Cells(.Rows.Count, 1).End(xlUp).Row
For Zeile = Zeile_L To 2 Step -1 '2 = 1. Zeile mit von-bis-Datum - ggf. anpassen
strDatum_von_bis = .Cells(Zeile, 1).Text
If InStr(strDatum_von_bis, "-") > 0 Then
If IsDate(Trim(Left(strDatum_von_bis, InStr(1, strDatum_von_bis, "-") - 1))) And _
IsDate(Trim(Mid(strDatum_von_bis, InStr(1, strDatum_von_bis, "-") + 1))) Then
Datum1 = CDate(Trim(Left(strDatum_von_bis, InStr(1, strDatum_von_bis, "-") - 1)))
Datum2 = CDate(Trim(Mid(strDatum_von_bis, InStr(1, strDatum_von_bis, "-") + 1)))
For Datum = Datum2 To Datum1 + 1 Step -1
.Cells(Zeile + 1, 1).EntireRow.Insert
.Cells(Zeile + 1, 1).Value = Datum
Next
.Cells(Zeile, 1).Value = Datum1
End If
End If
Next
End With
Application.ScreenUpdating = True
End Sub

Beliebteste Forumthreads (12 Monate)

10
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

313 Forumthreads zu ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

Tage zwischen zwei Datums

10 Beiträge zum Forumthread Beiträge zu diesem Forumthread

313 Forumthreads zu ähnlichen Themen

VBA Beispiele zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

10
Beiträge zum Forumthread

Beiträge zu diesem Forumthread