Data Validation using Dates
 

Have a list of dates from present through the end of year, usually only
Wednesday through Saturday in Book 1, and named the range of dates. Then I
opened a new we will call Book 2 and tried using Data Validation but all I
see in my dropdown box is the Excel numbers associated with those dates. Now
I went back and made sure that I had set each column in both books to the
proper date format. But alas nothing has changed. What I'm I doing wrong?
The format I would like to see the dates in is: 03/04/07.

Thanks

Steve

Data Validation using Dates
 

You have to format both as Date, the cell where the data validation occurs
and the cells where the List is.
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"caldog" wrote:

Have a list of dates from present through the end of year, usually only
Wednesday through Saturday in Book 1, and named the range of dates. Then I
opened a new we will call Book 2 and tried using Data Validation but all I
see in my dropdown box is the Excel numbers associated with those dates. Now
I went back and made sure that I had set each column in both books to the
proper date format. But alas nothing has changed. What I'm I doing wrong?
The format I would like to see the dates in is: 03/04/07.

Thanks

Steve

Data Validation using Dates
 

After re-reading my post I think I have left out some information. So here
goes.

In Book 1 I have named the range Dates
In Book 2 I have gone and done the following: Insert/Name/Define. Created a
new name [Date], and then in the refers to box typed this: =Book1.xls!Dates.
Now went to Data Validation option and selected list, and typed this =Date.

But again all i see is the following 36879.000 etc.

Steve

Data Validation using Dates
 

Michael both workbooks are formated in the same date format.

Data Validation using Dates
 

As per Michael's note: the DV cells in Book2 must be formated as Date.

I have repeated your actions and all works fine.

"caldog" wrote:

After re-reading my post I think I have left out some information. So here
goes.

In Book 1 I have named the range Dates
In Book 2 I have gone and done the following: Insert/Name/Define. Created a
new name [Date], and then in the refers to box typed this: =Book1.xls!Dates.
Now went to Data Validation option and selected list, and typed this =Date.

But again all i see is the following 36879.000 etc.

Steve

Data Validation using Dates
 

Well then I am at lost as to what is wrong, becasue I just checked again and
both books have the proper formatting in the columns that I am using the
dates in. Oh well will have to figure something else out then. Thanks for
your replys.

Steve

"Toppers" wrote:

As per Michael's note: the DV cells in Book2 must be formated as Date.

I have repeated your actions and all works fine.

"caldog" wrote:

After re-reading my post I think I have left out some information. So here
goes.

In Book 1 I have named the range Dates
In Book 2 I have gone and done the following: Insert/Name/Define. Created a
new name [Date], and then in the refers to box typed this: =Book1.xls!Dates.
Now went to Data Validation option and selected list, and typed this =Date.

But again all i see is the following 36879.000 etc.

Steve

Data Validation using Dates
 

Do you want to send books to:

toppers <at NOSPAMjohntopley.fsnet.co.uk (remove NOSPAM)

??

"caldog" wrote:

Well then I am at lost as to what is wrong, becasue I just checked again and
both books have the proper formatting in the columns that I am using the
dates in. Oh well will have to figure something else out then. Thanks for
your replys.

Steve

"Toppers" wrote:

As per Michael's note: the DV cells in Book2 must be formated as Date.

I have repeated your actions and all works fine.

"caldog" wrote:

After re-reading my post I think I have left out some information. So here
goes.

In Book 1 I have named the range Dates
In Book 2 I have gone and done the following: Insert/Name/Define. Created a
new name [Date], and then in the refers to box typed this: =Book1.xls!Dates.
Now went to Data Validation option and selected list, and typed this =Date.

But again all i see is the following 36879.000 etc.

Steve

Data Validation using Dates
 

Thanks Topper,

This is a project that I am working on from home, and now I am at work.
Maybe after opening it back up this evening it will work. If not then I
think I will take you up on ytour offer. Will let you know. And thanks
again for the offer.

Steve

"Toppers" wrote:

Do you want to send books to:

toppers <at NOSPAMjohntopley.fsnet.co.uk (remove NOSPAM)

??

"caldog" wrote:

Well then I am at lost as to what is wrong, becasue I just checked again and
both books have the proper formatting in the columns that I am using the
dates in. Oh well will have to figure something else out then. Thanks for
your replys.

Steve

"Toppers" wrote:

As per Michael's note: the DV cells in Book2 must be formated as Date.

I have repeated your actions and all works fine.

"caldog" wrote:

After re-reading my post I think I have left out some information. So here
goes.

In Book 1 I have named the range Dates
In Book 2 I have gone and done the following: Insert/Name/Define. Created a
new name [Date], and then in the refers to box typed this: =Book1.xls!Dates.
Now went to Data Validation option and selected list, and typed this =Date.

But again all i see is the following 36879.000 etc.

Steve

Data Validation using Dates
 

Topper,

Okay here is the latest, when I opened both spreadsheets, that I'm working
with. The sheet where I am selecting my dates from, when I select a cell an
click on the arrow to show all the dates available to me, all I see is Excel
number format, but IF I click on one of those numbers, then and only then
what I click on changes into a date. Therefore I have to quess what number
corresponds to what date.

And I double checked and both sheets have the same date format.

Steve