Changing Format of date (left to right as per image)

Question

I have these two date formats, would prefer it in the latter, i.e. on the right hand side.

My issue is that when the date is on the left it doesn't show up in other formula in my workbook and does when the format is on the right.

At the moment i'm using

= Format(Now(), "dd\/mm\/yyyy")

To enter todays date.

Double clicking in the cell work and text to columns ->finish both work but i don't know how to add that to my VBA.

Answer 1

Something like:

 With cell_ref
    .NumberFormat = "dd/mm/yyyy"
    .Value = Date
 End With