What is the VBA to convert a cell to the Australian date format

[dpaton05]

What is the VBA to convert a cell to the Australian date format of dd/mm/yyyy?

 

[Scott T]

Range("A1").NumberFormat = "dd/mm/yyyy"

 

[dpaton05]

How would I code in the change of the number format for a table column (A) instead of a range? This is a line of code I tried to write and put in the worksheet_open sub:

Worksheets("NPSS_quote_sheet").ListObjects("NPSS_quote").ListColumns(1).Range.NumberFormat = "dd/mm/yyyy"

Was I close?

 

[dpaton05]

I used the formula provided Scott, but it only converts the date once it is entered. By that I mean that I enter 1/23/19 (US format) into excel and it converts it to 23/1/19(Aus format). I want it to enter the date, 23/1/19 and for it to still be a date format as I have other cells that depend on it and they give me the #Value error if I try to enter the date as 23/1/19. If I enter the date as 1/23/19 it works fine but that's not what I want.

 

[James006]

Hello,

From what you are saying, you should close Excel and check your Windows Regional settings ... which are in fact used by all Office Applications ...

Hope this will help

 

[dpaton05]

Gee, that was so simple. Thank you!

 

[James006]

You are welcome ...:wink: