Dates change format alone

Question

My problem is pretty straightforward: I have a UserForm where the user insert two dates, the start_date and the end_date.
When the OK button (btnOK) is pressed, I want the column A populated from the start_date to the end_date.

This is the code:

Sub btnOK_Click()

     Dim myDate as Date
     Dim start_date as Date
     Dim end_date as Date
     Dim k, cont as Long

     start_date = Format(textStart.Value, "dd/MM/yyyy")
     end_date = Format(textEnd.Value, "dd/MM/yyyy")

     myDate = Format(start_date, "dd/MM/yyyy")
     k = 0
     cont = 1          ' Every 7 days i want 2 rows-space
     Range("A1").Select

     Do Until myDate = end_date + 1
          Selection.Offset(k, 0).Select
          Selection.Value = Format(myDate, "dd/MM/yyyy")
          myDate = format(myDate + 1, "dd/MM/yyyy")
          k = 0
          If cont = 7 Or cont Mod 7 = 0 Then
               k = k + 3
          Else
               k = k + 1
          End If
          cont = cont + 1
     Loop

End Sub

So, the code works fine, the only problem I have is some dates format. A little example here:

' Italian formatting
start_date = "30/09/2024"
end_date = "10/10/2024"

A	B	C
30/09/2024	Mon	dd/MM/yyyy
10/01/2024	Tue	MM/dd/yyyy
10/02/2024	Wed	MM/dd/yyyy
10/03/2024	Thu	MM/dd/yyyy
10/04/2024	Fri	MM/dd/yyyy
10/05/2024	Sat	MM/dd/yyyy
10/06/2024	Sun	MM/dd/yyyy
empty	empty	empty
empty	empty	empty
07/10/2024	Mon	dd/MM/yyyy
08/10/2024	Tue	dd/MM/yyyy
09/10/2024	Wed	dd/MM/yyyy
10/10/2024	Thu	dd/MM/yyyy

I don't know why, some rows are formatted as MM/dd/yyyy and anything I did helped me.
I can assure you that every cell in column A is formatted as italian date dd/MM/yyyy.

I've seen that I can make it work setting all dates variables as Strings, but I need them as dates for other formulas I use.
Any other suggestion about how make the same thing in a more efficient way would be greatly appreciated!

Answer 1

Remove all the formatting stuff - format is for display only:

Sub btnOK_Click()

     Dim myDate As Date
     Dim start_date As Date
     Dim end_date As Date
     Dim k As Long, cont As Long

     start_date = DateValue(textStart.Value)
     end_date = DateValue(textEnd.Value)

     myDate = start_date
     k = 0
     cont = 1          ' Every 7 days i want 2 rows-space
     Range("A1").Select

     Do Until myDate = end_date + 1
          Selection.Offset(k, 0).Select
          ' Apply desired format to the cell, not the value.
          Selection.Value = myDate
          myDate = myDate + 1
          k = 0
          If cont = 7 Or cont Mod 7 = 0 Then
               k = k + 3
          Else
               k = k + 1
          End If
          cont = cont + 1
     Loop

End Sub