combo box (form control) returns wrong date

fulmar57

New Member
Joined
Mar 10, 2019
Messages
8
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.
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,747
Office Version
  1. 365
Platform
  1. Windows
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
 
Last edited:

fulmar57

New Member
Joined
Mar 10, 2019
Messages
8
Thanks - I can see this works. But I'd be interested to know why doesn't the link cell display the correct date??
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,747
Office Version
  1. 365
Platform
  1. Windows
I have no idea - someone at Microsoft made that decision! :eek:
 

Watch MrExcel Video

Forum statistics

Threads
1,109,032
Messages
5,526,360
Members
409,697
Latest member
christopherlewis1620

This Week's Hot Topics

Top