index / match on date value

[projectile]

Hello

We have an ODBC dump of data (whcih gets refreshed each time sheet is opened), which includes a time stamp column, that displayss date in this format 2008-09-08 10:52:32

Now, on another sheet where the user types in a date in UK format e.g. 08/09/2008

We are looking for a formula that looks at the date entered in UK format (e.g. 08/09/2009 ) and matches this date only entered against ODBC time stamp in sheet2 column 2008-09-08 10:52:32, then returns adjustant value e.g. Rate of Exchange .

Please you advise how this is possible

We have tried to extract date from the ODBC dump using =DAY(B2) & "/" & MONTH(B2) & "/" & YEAR(B2) , however the index / match formula returns N/A

e.g. =INDEX(ROE!D:D,MATCH(DATEVALUE(B2),ROE!G:G,0))

in this forumla DATEVALUE(B2) is the field the user keys in a date

ROE!G:G is the ODBC date column in this format 2008-09-08 10:52:32

ROE!D:D column is the ODBC Rate of exhchange we want to return

Hope this make sense.

 

[Andrew Poulsom]

Have you tried INT(B2)?

 

[projectile]

Tried the int(B2) , but couldn't get to work,

Managed to get it working, however the user must enter the date without leading zeros, otherwise the match doesn't work.

Here is the method used;

in B2 (sheet called June) , set the field to TEXT in format. Enter this date 9/9/2008


in L2 (sheet called June) , entered this formula

=INDEX(Sheet1!$D:$D,MATCH(B2,Sheet1!$F:$F,0))

in sheet1 in column F, ran this formula down;

=DAY(B1) & "/" & MONTH(B1) & "/" & YEAR(B1)

This extracts the date only from the time stamp.



My question now is, is there a more efficient way to perform the above, so if the user enters 09/09/2008 (with leading zeros) it is able to do the match?

 

[Andrew Poulsom]

What's in Sheet1!$F:$F? Previously it was column G.

 

[projectile]

What's in Sheet1!$F:$F? Previously it was column G.

yes, it was G.

Sorry about the confusion