IFERROR Function not recognizing Date from Drop down calendar

[Chris McGlothen]

I have a drop down calendar that I want linked to a cell. In this case cell "B1". If I manually put the date in, the formula works, if I link the drop down calendar to the cell it doesn't pull the data over from the log to the report. All it is doing is on the report is looking for the date in Cell "B1" and if the dates in column "D" on the log match, it is pulling it over. Can someone tell me what I need to do? Thanks,

[bebo021999]

May be from date format between the 2 sheets? try date format in Log sheet follow mm/dd/yyyy

[Chris McGlothen]

Tried this, but it doesn't make a difference at all.

[MrShorty]

I never use calendars and array formulas like this, so there may be something I am overlooking. My first test was to put =ISTEXT(B1) into F1, which returned TRUE. I don't really understand what B1 is doing, but it is receiving the date as a text string and not as a real date value (review this if yoiu need a refresher on how Excel stores dates and times: http://www.cpearson.com/Excel/datetime.htm ). Replacing the reference to Report!$B$1 in each of the formulas with DATEVALUE(Report!$B$1) to convert the text date to a real date seemed to resolve the issue (DATEVALUE() help file) https://support.office.com/en-us/art...3-b7471bbff252

[Jonmo1]

How does that date picker get on the sheet?
It's just a dead picture to me, nothing happens when I click on it.

[ImranBhatti]

Is the macro acceptable?

[Chris McGlothen]

Jonmo1, the calendar goes to the top left of the sheet for some reason? I have never embedded a calendar into excel before.

[Jonmo1]

What I meant is where did it come from ? How did you put it there ?

[Chris McGlothen]

Using the developer tab,design mode,insert, more controls, and then clicking Microsoft Date and Time Picker Control 6.0 (SP6)

[Jonmo1]

Yep, that date picker control is indeed entering the date as a Text String.

Try adjusting your formulas to do +0 on the date cell.
Formula in A4, change from
=IFERROR(INDEX(Log!A$1:A$1000,SMALL(IF(Log!$D$1:$D$1000=Report!$B$1,ROW(Log!$D$1:$D$1000)-MIN(ROW(Log!A$1:A$1000))+1),ROWS($1:1))),"")
to
=IFERROR(INDEX(Log!A$1:A$1000,SMALL(IF(Log!$D$1:$D$1000=Report!$B$1+0,ROW(Log!$D$1:$D$1000)-MIN(ROW(Log!A$1:A$1000))+1),ROWS($1:1))),"")

[Chris McGlothen]

Thanks a million Jonmo1. Do you happen to know why every time I save the file an re-open the calendar drop down box is moved to the top left of the screen? If I go back to the developer mode and and click on the calendar and get back out of the design mode it goes back to normal, but process is repeated after every save?

[Jonmo1]

You're welcome.

No I have no ideas on that.

[Chris McGlothen]

It appears to be a Microsoft Office 2010 and newer issue. I found some code to make a new calendar. Thanks again