Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works
Photo

Excel: hlookup date


  • Please log in to reply

#1
Lady Cygnus

Lady Cygnus

    New Member

  • Member
  • Pip
  • 9 posts
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?
  • 0

Advertisements


#2
Jonesey

Jonesey

    Member

  • Member
  • PipPipPip
  • 335 posts
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.
  • 0

#3
Lady Cygnus

Lady Cygnus

    New Member

  • Topic Starter
  • Member
  • Pip
  • 9 posts
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


  • 0

#4
Jonesey

Jonesey

    Member

  • Member
  • PipPipPip
  • 335 posts
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)))
  • 0

#5
Lady Cygnus

Lady Cygnus

    New Member

  • Topic Starter
  • Member
  • Pip
  • 9 posts

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.
  • 0

#6
Jonesey

Jonesey

    Member

  • Member
  • PipPipPip
  • 335 posts
No problem.

Glad you got it sorted
  • 0






Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP