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?
 
J79123Connect With a Mentor Author 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
 
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
 
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
All Courses

From novice to tech pro — start learning today.