amzeek
08-13-2015, 12:05 AM
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!
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!