Formatting the contents of a referenced cell in Excel

Cell W17 contains the following content:  33115. The cells displays as 03-31-15 through the use of a custom format.

I want to reference the cell in cell C17 which contains the following text:  ="The end period date is "&W17&"."

But that displays as
                                        The end period date is 33115.  
How can I format the outcome to be
                                      The end period date is 03--31-15.
LVL 1
Bill GoldenExecutive Managing MemberAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
Try

="The end period date is "&Text(W17,"dd/mmm/yyyy")&"."

You can change the date format to suit.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Maybe this.....

="The end period date is "&TEXT(W17,"00-00-00")&"."

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Don ThomsonCommented:
Format the Cell W17  as text
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
@DTHConsulting

He has custom formatted W17, probably you missed that.
Roy CoxGroup Finance ManagerCommented:
Excel stores dates as a serial number that represents the number of days that have taken place since the 01/01/1900. This means that January 1st 1900 is really just a 1. January 2nd 1900 is 2.  The number 33115 in W17 represents the 31st, March 2015.

Importantly, any date before January 1st 1900 is not recognized as a date in Excel. There are no “negative” date serial numbers on the number line.

The formula is pulling the actual value  in W17 and the TEXT function converts it to a Text date
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
@Roy

The actual cell content in W17 is 33115 and if you format this cell to Short Date, it will be converted into 30/08/1990. So obviously it is not what OP is trying to show in the formula cell.

If OP uses your formula, the output he will get will be as below.....

The end period date is 30/Aug/1990.

Open in new window

not what he is looking for and which is...

The end period date is 03--31-15. 

Open in new window

Or am I missing something?
Roy CoxGroup Finance ManagerCommented:
I assumed the date was pulling through the serial date. If that's not the case then I would expect problems trying to display some dates e.g 30th September 2015. If dates are being added like that then I would suggest using Chip Pearson's Function

Quick Date Entry
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
@Roy

What I think that incorporating a VBA code to show the date in the desired format is unnecessary in this case when the OP has already set the custom format to show the entered date in the desired date format which is quite easy rather than doing this via a VBA code.

Its good to have multiple options to get the same desired output but if the OP is comfortable with the current setup, let's not deviate from the original question because the question he asked about how to show the desired output in the formula cell but not how to show the dates in desired format in the source cell which he is already getting with the custom format. :)
Bill GoldenExecutive Managing MemberAuthor Commented:
Sorry, to take so long to chime back in...

Roy, DTH, Sktneer was right.  I do have the date stored as a custom format.  

[>99999]##-##-##;"0"#-##-##

So everything has to be outside of Excel's date function.  I sort of inherited some of this so I am living with what has gone before.

Sktneer's first solution worked perfectly.  It is interesting that you can only use a - as the separator. If you use / , you get a #VALUE! response.  Not sure why.

Maybe I should modify the custom format if I want to use a different separator.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.