[Lady Cygnus]

I have an excel spreadsheet with a table like so:

Issue DOI 8/31/08 9/7/08 9/14/08 9/21/08 Rank
64_2 12 0 2 4 3 2
64_3 full 0 0 1 4 3
64_1 1 1 9 8 6 1

I want to fill the following table:

Issue Live Week 1 Week 2 Week 3 Week 4 Rank
64_1 8/31 2 4 3 - 2
64_2 9/7 1 4 - - 3
64_3 9/14 1 9 8 6 1

I thought to do so with hlookup - but it doesn't work on the dates.

1. Why doesn't hlookup work on dates?
2. Any suggestions for what might work?

[Advertisements]

Sorry, I'm having trouble understanding what you table actually looks like - you might be better off capturing a screen shot of it and attaching it to your next reply.

But----- Hlookups DO work on dates, absolutely no question.

So my questions are:

1 - Are you SURE it's DATES you're looking at, and not text which looks like a date?
2 - Is there an exact match between your lookup & the table? If not, Excel has ways in which to interpret the result

Syntax

HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)


If HLOOKUP can't find lookup_value, and range_lookup is TRUE, it uses the largest value that is less than lookup_value.


If lookup_value is smaller than the smallest value in the first row of table_array, HLOOKUP returns the #N/A error value.

[Jonesey]

Sorry, I'm having trouble understanding what you table actually looks like - you might be better off capturing a screen shot of it and attaching it to your next reply.

But----- Hlookups DO work on dates, absolutely no question.

So my questions are:

1 - Are you SURE it's DATES you're looking at, and not text which looks like a date?
2 - Is there an exact match between your lookup & the table? If not, Excel has ways in which to interpret the result

Syntax

HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)


If HLOOKUP can't find lookup_value, and range_lookup is TRUE, it uses the largest value that is less than lookup_value.


If lookup_value is smaller than the smallest value in the first row of table_array, HLOOKUP returns the #N/A error value.

[Lady Cygnus]

Sorry about that, I was running out the door and didn't check to make sure the tabs worked. I've attached an example spreadsheet (tab delimited text file with equations shown). The first has A2:K8 defined as table_64, the second uses references.

I'm quite certain there is an issue with looking like a date not actual being a date, but I have no idea how to fix it. The dates in the header originally came from a csv file in the format 31-Aug-08, which Excel then recognized them as 2 digit years and asked to convert them to four-digit year - I complied.

I've tried:

• If I copy the cells with the dates into the reference cell for the equation the hlookup will work, but typing it in (or changing it using F2 to try to keep the format) won't work.
• reformatting the header dates (General, Date, number, etc)
• adding zero to the header dates (to convert them to numbers)
• using date(year(), month(), day()) to no avail
• I also tried recreating the table and hard coding the header dates - which causes hlookup to not find the date and go to the last column.

What am I doing wrong?

Attached Files

•  test.txt   2.94KB   316 downloads
•  test_notable.txt   2.05KB   264 downloads

[Jonesey]

Hmmm. Could be a number of things, but I suspect as you do, it's the fact that Excel is not recognising them as dates which is causing the problems.

This is being complicated my end, because we use a different date notation to yourself

We use dd/mm/yyyy as opposed to mm/dd/yyyy


BUT, hopefully we can overcome that as follows.

This is quite a complex formula, but it will covert a date entered as text, in mm/dd/yyyy format into a standard Excel date which you can then reference in formulae etc.

The formula assumes that your text-date is in cell A1

Have a play with it and see if it goes any way towards solving your problem.


=DATE(VALUE(RIGHT(A1,LEN(A1)-FIND("/",A1,FIND("/",A1)+1))),VALUE(LEFT(A1,FIND("/",A1)-1)),VALUE(MID(A1,FIND("/",A1)+1,FIND("/",A1,FIND("/",A1)+1)-FIND("/",A1)-1)))

[Lady Cygnus]

Hmmm. Could be a number of things, but I suspect as you do, it's the fact that Excel is not recognising them as dates which is causing the problems.

The formula assumes that your text-date is in cell A1

Have a play with it and see if it goes any way towards solving your problem.


=DATE(VALUE(RIGHT(A1,LEN(A1)-FIND("/",A1,FIND("/",A1)+1))),VALUE(LEFT(A1,FIND("/",A1)-1)),VALUE(MID(A1,FIND("/",A1)+1,FIND("/",A1,FIND("/",A1)+1)-FIND("/",A1)-1)))

The formula worked to convert the dates, but the table kept converting them back to text. I finally got it to work by converting the table to a range and converted the dates using your formula.

It appears that the tables in 2007 convert the dates to text.

Thank you for your help.

[Jonesey]

No problem.

Glad you got it sorted