Excel 2002 Date Format in Data Validation List

I have a list of dates that people should be allowed to select from. the cell is with the validation is formatted as a date cell, so it looks fine after someone has selected a date.
But... the list shows the excel value, not in date format. I need it to show the list in date format, because otherwise people aren't going to have a clue what to pick.

The list it's pulling from is in date format, so that's not the issue. And I have calculations based on this selection, so I can't change it to words.
Screen shot attached of the issue

This doesn't seem to affect excel 2007 or 2010. But I need a workaround for 2002.
vba is ok, but please make it easy because anything too complex just breaks. Thx :)
date-format-issue.png
LVL 6
J79123Asked:
Who is Participating?
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.

dandrakaCommented:
Listen....it is preferable that your values are derived from a list which contains the dates and is formatted as dates (see attached example)..if you have a range of dates that are to be the validation dates, you can use the build in feature of Excel's validation to do this as well.
datevalidate.xls
0
J79123Author Commented:
My list is formatted as dates and I still get the attached issue.
0
TracyVBA DeveloperCommented:
Can you upload your workbook?  

I'm not getting what you're getting, I'm getting whatever the list is formatted as in the drop down.  Maybe there's some hidden formats or something that's causing this.
0
J79123Author Commented:
Are you using excel 2002? I'm guessing no, because believe me, I'm not using it by choice. :) It's an excel 2002 issue.
I'm actually going to use my own work around and am going to pull this question.. I'm added a space to make it seem like text to excel, then used a vlookup to the real values in my formulas.

Thanks for your help
0

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
dandrakaCommented:
or maybe you dont want to give 500 points for something that was this simple to begin with....I've tried it in 2002 and it works fine there too...anyway...
0
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.