On European Excel the date format doesnt change

[columkelly]

Date format incorrect on DDE.xls

Hello,

When I right click, date and then change the date format it does not change in the excel cell i highlighted it stays the same.

For excel within column D this formula is there "=vttrader|EURUSD!date" what is displayed on my spreadsheet is 01/13/2005 - which under the American date system is fine. I would like it to display 13/01/2005

However when i try convert it to European by right click, format, date and pick all of the fields in column D within the selection box on the right, no change occurs. No change even occurs if you pick a settings in the custom field below. The excel spreadsheet doesnt even recognise it as a date anymore.

It is frustrating because i have a trading system that works off the VT live feed for calculating time cycles.

As a control test, I have typed today’s date in this format 14/1/2005 in cell G10 and then by right clicking, number tab, custom settings I changed it to the American format i.e. mm/dd/yyyy = 1/14/2005 successfully.

I then linked cell G10 into cell G12 and changed the formatting once again by right clicking to dd/mmmm/yyyy = 14 January 2005 successfully.

I then tried this experiment with the visual trader formula, by both highlighting the whole column and also the single cell.

The results are displayed in cells I10 and I12 "01/14/2005" it did not change format in any way.

Can this be changed by changing my settings in my excel properties? English (Ireland) to English (American) or is there another way.

I appreciate the time and effort you have performed in answering my queries.

How can I change the date format to european.

Any help would be appreciated.

Kind Rgds and respect,

Colum

 

[Norie]

Colum

Try using a custom format like dd/mm/yy.

BTW if you are using the date in some calculation within Excel it shouldn't actually matter how it is displayed.

 

[fairwinds]

Hi,

It seems to me your source data is text formatted US dates. If so, try a formula like:

=DATE(RIGHT(A2,4),SUBSTITUTE(LEFT(A2,2),"/",""),SUBSTITUTE(MID(SUBSTITUTE(A2,"/","//"),4,3),"/",""))

and format as you like.