Date Formatting Issue

[amzeek]

Hello,
I have an excel cell each for StartDate and EndDate with the format dd/MM/yyyy. After the user enters these dates I am required to create a cell for each day from the start date to end date entered by the user.
For some reason the format on each of these cells is getting changed to MM/dd/yyyy from dd/MM/yyyy

For example:
StartDate 15/01/2015
EndDate 17/01/2015

Cells created: 01/15/2015, 01/16/2015, 01/17/2015

Expected: 15/01/2015, 16/01/2015, 17/01/2015

Code:


Dim startDate$
Dim endDate$


startDate = Format(Date, "dd/MM/yyyy")
endDate = Format(Date, "dd/MM/yyyy")
startDate = Sheet23.Cells(2, 9).Value
endDate = Sheet23.Cells(3, 9).Value

'Validation for start date > end date etc and then calculate the date diff

days = DateDiff("d", startDate, endDate)
days = days + 1

Set DCCSCell = Sheet23.Cells(4, 10) 'J4
i = 1
Do Until i = days + 1
DCCSCell.offset(2, i).Value = startDate 'This is where the format changes
startDate = Format(DateAdd("d", 1, startDate), "dd/MM/yyyy")
i = i + 1
Loop

Please advise!
Thanks!

[mancubus]

welcome to the forum.

check your pc settings. if correct, below code will work...



Sub vbax_53451_InsertDates()

Dim startDate As Date, endDate As Date
Dim i As Long

With Sheet23
startDate = .Cells(2, 9).Value
endDate = .Cells(3, 9).Value

'check if start date >= end date
If endDate <= startDate Then
MsgBox "End date must be later than start date!" & vbLf & vbLf & "Quitting...", vbOKOnly + vbCritical, "Error"
Exit Sub
End If

For i = 1 To (endDate - startDate)
.Cells(4, 10).Offset(2, i).Value = startDate + i
Next
End With

End Sub

[p45cal]

try:
deleted; wrong

[p45cal]

sorry, mistake. Try:
With Sheet23.Range("K6")
.Value = Sheet23.Cells(2, 9).Value
Set Destn = .Resize(, Sheet23.Cells(3, 9).Value - Sheet23.Cells(2, 9).Value + 1)
.AutoFill Destn
Destn.NumberFormat = "dd/mm/yyyy"
End With