Date imported from another workbook, change display format

I am pulling in a date from another workbook via ='[am3.xlsx]am3-Peu used (VS)'!$F$1

It is coming over as 30/09/09 which is how it is formatted on the other workbook.

I want to display it as Sept 09.

I have tried playing with the cell formatting (in the cell with the formula) but it stays the same 30/09/09.

Any ideas?

Regards BVG

Sounds to me like it's text, even though it looks like a date. Check how it's entered in the source workbook.

Originally Posted by Badvgood

The cell the data is coming from is set as general...

But that's not the point. My comment was unrelated to the cell format. The data itself could be text data rather than numeric (date) data.

This might be resolved quickly if you can post both workbooks.

Extending 6String's post... what happens if you modify the link to:

=0+'[am3.xlsx]am3-Peu used (VS)'!$F$1

and subsequently format as mmm-yy

FWIW it would also help if you added your locale to your profile... date formats for one are affected (significantly) by this.

Originally Posted by Badvgood

Adding the +0 worked perfectly.

How does it do this tho?

This indicates that the source of your data is text, and not numeric. Putting the "0+" forces the formula to convert the text to a date, and then your formatting will take effect. As long as the data is text, then applying data or numeric formatting will have no effect.

If you go back to the original workbook and select the cell with the date, and look in the formula bar (or press F2), you will probably see

'30/09/09

The initial ' indicates this is entered as text, and not a date.