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:
This gives: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
Input Box: 21/03/2019
Prints in excel cell: 03/21/2019
So then, I tried correcting the printing of the excel date using:
Which does indeed give the correct date for both instances.Ch.Cells(1, 10).NumberFormat = "mm/dd/yyyy"
Ch.Cells(1, 10) = del
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.
Bookmarks