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
Last edited by Badvgood; 03-05-2010 at 11:25 AM.
Sounds to me like it's text, even though it looks like a date. Check how it's entered in the source workbook.
Making the world a better place one fret at a time
||||||
If someone helped you, please click on the star icon at the bottom of their post
If your problem is solved, please update the first post:
EDIT, Go Advanced button, set Prefix to SOLVED
[code]
' Enclose code in tags like this
[/code]
Don't attach a screenshot--just attach your Excel file! It's easier and will let us experiment with your data, formulas, and code.
Hi, no tried that.
The cell the data is coming from is set as general and the cell where I need the date to appear where the formula is is set to date, where I have tried different formatting but with no luck.
Any other ideas?
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.Originally Posted by Badvgood
This might be resolved quickly if you can post both workbooks.
Making the world a better place one fret at a time
||||||
If someone helped you, please click on the star icon at the bottom of their post
If your problem is solved, please update the first post:
EDIT, Go Advanced button, set Prefix to SOLVED
[code]
' Enclose code in tags like this
[/code]
Don't attach a screenshot--just attach your Excel file! It's easier and will let us experiment with your data, formulas, and code.
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.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Adding the +0 worked perfectly.
How does it do this tho?
Thanks BVG
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.
Making the world a better place one fret at a time
||||||
If someone helped you, please click on the star icon at the bottom of their post
If your problem is solved, please update the first post:
EDIT, Go Advanced button, set Prefix to SOLVED
[code]
' Enclose code in tags like this
[/code]
Don't attach a screenshot--just attach your Excel file! It's easier and will let us experiment with your data, formulas, and code.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks