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.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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:
Upvote 0
Thanks - I can see this works. But I'd be interested to know why doesn't the link cell display the correct date??
 
Upvote 0
I have no idea - someone at Microsoft made that decision! :eek:
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top