combo box (form control) returns wrong date

[fulmar57]

Hi

I have a list of dates in E5 to E7, in long date format. I have a form control with an input range of E5 to E7.

The cell M5 also has long date format and is the cell link for the format control.

Cell M5 does not display the correct dates from the dropdown list and instead displays 1,2 or 3 January 1900 ??? Correct dates are 1 Jan 2017, 1 March 2017, 1 June 2018

I tried this in another spreadsheet and the days and months were correct but the year was 1900.

Many thanks for any advice you can give.

 

[Yongle]

The link cell returns which option user selected

So if you want the date selected in the combobox ..
use this formula
=INDEX(E5:E7,M5)
and format the cell as a date

For something as simple as this consider using data validation with the same input range for the DV list - the cell returns the date which is what you appear to want

 

[fulmar57]

Thanks - I can see this works. But I'd be interested to know why doesn't the link cell display the correct date??

 

[Yongle]

I have no idea - someone at Microsoft made that decision!

 

[fulmar57]

OK - thanks for your help.