Combo Box-date format

P

pedro AM

Hi all

My issue is that I have a combo box linked to months in cells R6:R16. These
are in the format May-04,June-04,etc.
When I click on the box I have the choice as I expect but once I have
selected the month it returns the excel number like 38139. I do not mind this
on the linked cell where my lookups are looking at but in the interface which
is what I see, I have the number and not the date.

How can I change it so it shows the date and not the number? I have tried
with the format-cells options anf the properties box where both text and
value appear as a 5 figure number.

Any help would be much appreciated.

Thanks
 
D

Debra Dalgleish

The combobox returns a string to the cell. In another cell, you could
use a formula to force it to a number, e.g. =G2+0
Format that cell as a date, and hide the column with the linked cell.
 
P

pedro AM

Hi Debra

Thanks for your prompt reply . I do not think that that is a solution.
Correct me if I am wrong but in that case I would have a number in my combo
box and another cell with a Date, which is the equivalent to the number in
that box.

However, I do not want both; I wanted the combo box to show the date because
it is part of the report.So I need the combo box to show the date on it.

Can it happen?

Thanks
 
D

Debra Dalgleish

Instead of using a list of dates as the fillRange, use a list of
formatted dates.

For example, if your dates are in cells A1:A20, enter the following
formula in cell B1:
=TEXT(A1,"dd-mmm-yy")
Copy the formula down to B20
Change the combobox FillRange to B1:B20
 
Top