Printing Date Issues

[RLS881103]

So I am having issues with the excel/VBA giving dates in the US format (mm/dd/yyyy), even though all of my settings are set to Australian format (dd/mm/yyyy).

Essentially I have an Input box for a date, and without any format changes, the input box displays the US date, but then when it is printed into a cell on the excel spreadsheet, it gives the Australian format. As an example, for 21st of March, it supplies:

Input Box: 03/21/2019
Prints in excel cell: 21/03/2019

So I adjust the format of the Input Box to display the date in Australian format using:

If Weekday(Date) = 6 Then
dmy = Date + 3
Else
dmy = Date + 1
End If

del = Application.InputBox("Enter Delivery Date", TitleMsg, Format(dmy, "dd/mm/yy"))

Ch.Cells(1, 10) = del

This gives:

Input Box: 21/03/2019
Prints in excel cell: 03/21/2019

So then, I tried correcting the printing of the excel date using:

Ch.Cells(1, 10).NumberFormat = "mm/dd/yyyy"
Ch.Cells(1, 10) = del

Which does indeed give the correct date for both instances.

Input Box: 04/03/2019
Prints in excel cell: 04/03/2019

But then in later code, when I try to use the date that has been printed, everything gets switched around again. So just wanted to see if anyone had a solution how to correct the original InputBox, because it seems that is just creates a domino effect the more I try to fix anything else.

Thanks.

[Norie]

Try using CDate or DateValue.

Please Login or Register  to view this content.

[RLS881103]

I tried the below...

Please Login or Register  to view this content.

Is this what you meant? Because it did not work, it still returned the date in Ch.Cells(1,10) in the US format.

Thanks.

[RLS881103]

Actually I managed to get your suggestion to work. It also sparked the realisation in me that maybe the actual Date formated to the cell was in the incorrect format. And it was, by changing the cell's format back to General, the Australian Date was displayed.

Thanks.