just be careful with using Format(Date,"dd/mm/yyyy"), if you put this result directly into a cell its likely going to mix up the dd/mm part depending on the date you use. norie's suggestion is good but you must convert it into the correct type first.....
either enter into the sheet as a mm/dd/yyyy format first and let the formatting handle the conversion into dd/mm/yyyy
or you can convert it into the format excel uses internally with CDbl(CDate("10/10/2016 10:30PM")) this will give you a number that excel will always understand as the correct date you just need to add a cell format to format it how you want it, but even this method is not reliable..
just to show you how weird this gets run this code
Sub test()
Debug.Print (CDbl(CDate("10/10/2016 10:30PM")))
Debug.Print (CDbl(CDate("25/10/2016 10:30PM")))
Debug.Print (CDbl(CDate("10/25/2016 10:30PM")))
Debug.Print (CDate("10/10/2016 10:30PM"))
Debug.Print (CDate("25/10/2016 10:30PM"))
Debug.Print (CDate("10/25/2016 10:30PM"))
End Sub
this is what i got
42653.9375
42668.9375
42668.9375
10/10/2016 10:30:00 PM
10/25/2016 10:30:00 PM <- as you can see CDate changes the UK format date into US format
10/25/2016 10:30:00 PM
since your not doing calculations on the date just set the cells to text and just assume whatever text is in the textbox is the right format....
Bookmarks