Problem with Date format when copied via VBA

Dear all

I am currently working on a timetracking tool based on excel.

There is also an option to archive current projects and also de-archive if needed. Further, the entries which have been archived are being analysed with a couple of functions which result in an overview report. E.g. looking for specific entries with date value > respectively < then a specific selected date ("=SUMIFS(Archiv!C:C;Archiv!B:B;">"&Woche!$S$8;Archiv!B:B;"<"&Woche!$S$9;Archiv!I:I;Woche!R18)".

However, I have noticed, that the format for the date section in the archive is somehow not being realized by the above function, while the cells are being formated as "date" and not "general" or "text" or something. I have read that the date is not being recognized as date anymore when transferred via VBA, even if the format says "date" (weird?). Being said, when I manually reenter the dates in the respective archive column, the function recognizes the dates and works (even more weird?). I am new to excel and would really appreciate your help!

My VBA code (1) fills a listbox with the current time entries - the relevant sections looks like this, where as the date part is marked bold:

For i = 1 To 20
If ThisWorkbook.Sheets("Zeiterfassung").Cells(2 + i, 31).Value = "RDY" Then
UserForm41.ListBox1.AddItem i
UserForm41.ListBox1.List(UserForm41.ListBox1.ListCount - 1, 1) = Format(ThisWorkbook.Sheets("Zeiterfassung").Cells(2 + i, 25), "dd/mm/yyyy")
UserForm41.ListBox1.List(UserForm41.ListBox1.ListCount - 1, 2) = Format(ThisWorkbook.Sheets("Zeiterfassung").Cells(2 + i, 26), "0.0")
UserForm41.ListBox1.List(UserForm41.ListBox1.ListCount - 1, 3) = Format(ThisWorkbook.Sheets("Zeiterfassung").Cells(2 + i, 27), ">")
UserForm41.ListBox1.List(UserForm41.ListBox1.ListCount - 1, 4) = Format(ThisWorkbook.Sheets("Zeiterfassung").Cells(2 + i, 32), "")
UserForm41.ListBox1.List(UserForm41.ListBox1.ListCount - 1, 5) = Format(ThisWorkbook.Sheets("Zeiterfassung").Cells(2 + i, 33), "0000")
UserForm41.ListBox1.List(UserForm41.ListBox1.ListCount - 1, 6) = Format(ThisWorkbook.Sheets("Zeiterfassung").Cells(2 + i, 28), "")
UserForm41.ListBox1.List(UserForm41.ListBox1.ListCount - 1, 7) = Format(ThisWorkbook.Sheets("Zeiterfassung").Cells(2 + i, 29), "")
UserForm41.ListBox1.List(UserForm41.ListBox1.ListCount - 1, 8) = Format(ThisWorkbook.Sheets("Zeiterfassung").Cells(2 + i, 30), "")
End If
Next i

(2) the selected entries will be put to the archive. The section of the code looks like this:

For n = 0 To UserForm41.ListBox1.ListCount - 1
If UserForm41.ListBox1.Selected(n) = True Then
'Einträge aus Listbox (selected) nach tab "Archiv" kopieren
ThisWorkbook.Sheets("Archiv").Cells(ArchivNR, 2) = Format(UserForm41.ListBox1.List(n, 1), "dd/mm/yyyy")
ThisWorkbook.Sheets("Archiv").Cells(ArchivNR, 3) = UserForm41.ListBox1.List(n, 2) 'EY Zeit
ThisWorkbook.Sheets("Archiv").Cells(ArchivNR, 4) = UserForm41.ListBox1.List(n, 3) 'Kunde
ThisWorkbook.Sheets("Archiv").Cells(ArchivNR, 5) = UserForm41.ListBox1.List(n, 4) 'E-Code
ThisWorkbook.Sheets("Archiv").Cells(ArchivNR, 6) = Format(UserForm41.ListBox1.List(n, 5), "0000") 'Activity
ThisWorkbook.Sheets("Archiv").Cells(ArchivNR, 7) = UserForm41.ListBox1.List(n, 6) 'Projekt
ThisWorkbook.Sheets("Archiv").Cells(ArchivNR, 8) = UserForm41.ListBox1.List(n, 7) 'Projekt
ThisWorkbook.Sheets("Archiv").Cells(ArchivNR, 9) = UserForm41.ListBox1.List(n, 8) 'Projekt

End If
Next

For example, I have archived 2 entries (yellow):Capture.PNG
The first entry was manually changed by myself (retyped the date into the cell) and is being recognized by my functions as date. The yellow ones are shown as date, but are not being recognized as such.

Looking forward to your answers!

Amazing! Thank you so much, it works perfectly fine now.

Cheers, have a good one