Changing date format in formula

I have the following formula where $B$5 is a date in the format mm/dd/yyyy. The formula is matching the date value in $B$5 with the date values in $Z$4:$Z$1000. The problem is that the date values in $Z$4:$Z$1000 are in the format mm/dd/yyyy hh:mm:ss am/pm so no matches are found. How can I change the formula to find an appropriate match if the mm/dd/yyyy are the same?



=IFERROR(INDEX('Enter Executions'!$C$4:$C$1000,SMALL(IF('Enter Executions'!$B$4:$B$1000=$B$4,IF('Enter Executions'!$Z$4:$Z$1000=$B$5,ROW('Enter Executions'!$B$4:$B$1000)-ROW('Enter Executions'!$B$4)+1)),ROWS($A$1:$A1))),"")

Excel doesn't look at the format. If one cell is formatted as dd/mm/yy and the other as mm/dd/yy, Excel just sees the underlying number. What is probably happening in your case is that one of the two ranges is formatted as text and the other as number (or both are formatted as text). To check, (let's say your date is in A1), in a blank cell, type =ISNUMBER(A1). If it's true, it's a number.

You can extract a date from a date/time using INT function, so try replacing this part

'Enter Executions'!$Z$4:$Z$1000

with

INT('Enter Executions'!$Z$4:$Z$1000)

Ahhh, reread your original post. Because you have hours on the one range, that will cause them not to match.

Whenever referring to your data in Z, use INT to truncate the hours and minutes
i.e.
INT('Enter Executions'!$Z$4:$Z$1000)=$B$5

I tried changing it to IF(INT('Enter Executions'!$Z$4:$Z$1000)=$B$5 and I recieved an error. Any ideas what I am doing wrong?


{=IFERROR(INDEX('Enter Executions'!$C$4:$C$1000,SMALL(IF('Enter Executions'!$B$4:$B$1000=$B$4,IF(INT('Enter Executions'!$Z$4:$Z$1000=$B$5,ROW('Enter Executions'!$B$4:$B$1000)-ROW('Enter Executions'!$B$4)+1)),ROWS($A$1:$A1))),"")}

It might depend on what you have in that range - INT will cause an error if applied to a text value, for instance. An alternative approach (which won't give an error in those circumstances) is to check that the Z range contains a value >=B5 but < B5+1, i.e. it's on that day......

=IFERROR(INDEX('Enter Executions'!$C$4:$C$1000,SMALL(IF('Enter Executions'!$B$4:$B$1000=$B$4,IF('Enter Executions'!$Z$4:$Z$1000>=$B$5,IF('Enter Executions'!$Z$4:$Z$1000<$B$5+1,ROW('Enter Executions'!$B$4:$B$1000)-ROW('Enter Executions'!$B$4)+1))),ROWS($A$1:$A1))),"")

confirmed with CTRL+SHIFT+ENTER as before