Looking up a value based on date and time, what's wrong?
 

I put this function:
(INDEX('2000'!$A$9:$I$8768,(DAY(analysis2000!$A2)-1)*24+1+analysis2000!B$1,3))*0.447
in a worksheet called analysis2000.
This looks up a value in another worksheet (called 2000) based on the
date, which is stored in analysis2000!A2. I copied this out from one
working on a worksheet called 1999. It worked fine there. But for this
one, Excel 2000 returns the VALUE error on every instance. What I am
trying to do is putting a long list of hourly data (for a whole year)
into a matrix, one row for each day of the year. So 2000 has about 9000
rows, analysis2000 has 366.
The problem is in the DAY part of the function, which returns this
error. Can anyone shed some light for me?
Thanks in advance.
Margo Guda.

Looking up a value based on date and time, what's wrong?
 

Margo, is there any chance that the "date" in cell analysis2000!A2 is
actually a piece of text *pretending* to be a date?
Rgds,
ScottO

"Margo Guda" wrote in message
...
| I put this function:
|
(INDEX('2000'!$A$9:$I$8768,(DAY(analysis2000!$A2)-1)*24+1+analysis200
0!B$1,3))*0.447
| in a worksheet called analysis2000.
| This looks up a value in another worksheet (called 2000) based on
the
| date, which is stored in analysis2000!A2. I copied this out from
one
| working on a worksheet called 1999. It worked fine there. But for
this
| one, Excel 2000 returns the VALUE error on every instance. What I
am
| trying to do is putting a long list of hourly data (for a whole
year)
| into a matrix, one row for each day of the year. So 2000 has about
9000
| rows, analysis2000 has 366.
| The problem is in the DAY part of the function, which returns this
| error. Can anyone shed some light for me?
| Thanks in advance.
| Margo Guda.

Looking up a value based on date and time, what's wrong?
 

I think that may be it, although how that can happen is a mystery to me.
I copied it from the series of dates for 1999, only changing 1999 to
2000 using edit|replace. How could a date become a piece of text
pretending to be a date? And what's more important, how do I get it back
to being a date? When I format the cell to be a date, nothing really
happens.

Margo.

ScottO wrote:
Margo, is there any chance that the "date" in cell analysis2000!A2 is
actually a piece of text *pretending* to be a date?
Rgds,
ScottO

"Margo Guda" wrote in message
...
| I put this function:
|
(INDEX('2000'!$A$9:$I$8768,(DAY(analysis2000!$A2)-1)*24+1+analysis200
0!B$1,3))*0.447
| in a worksheet called analysis2000.
| This looks up a value in another worksheet (called 2000) based on
the
| date, which is stored in analysis2000!A2. I copied this out from
one
| working on a worksheet called 1999. It worked fine there. But for
this
| one, Excel 2000 returns the VALUE error on every instance. What I
am
| trying to do is putting a long list of hourly data (for a whole
year)
| into a matrix, one row for each day of the year. So 2000 has about
9000
| rows, analysis2000 has 366.
| The problem is in the DAY part of the function, which returns this
| error. Can anyone shed some light for me?
| Thanks in advance.
| Margo Guda.

Looking up a value based on date and time, what's wrong?
 

Here is some mo
It must be a problem with excel from office 2000, because on another
machine which has office 2003 installed the problem does not exist and I
get exactly what I needed for the analysis2000 table. So, what gives? Is
this a Y2K bug in Excel 2000?? It does not recognize dates after 1999??

Margo

ScottO wrote:
Margo, is there any chance that the "date" in cell analysis2000!A2 is
actually a piece of text *pretending* to be a date?
Rgds,
ScottO

"Margo Guda" wrote in message
...
| I put this function:
|
(INDEX('2000'!$A$9:$I$8768,(DAY(analysis2000!$A2)-1)*24+1+analysis200
0!B$1,3))*0.447
| in a worksheet called analysis2000.
| This looks up a value in another worksheet (called 2000) based on
the
| date, which is stored in analysis2000!A2. I copied this out from
one
| working on a worksheet called 1999. It worked fine there. But for
this
| one, Excel 2000 returns the VALUE error on every instance. What I
am
| trying to do is putting a long list of hourly data (for a whole
year)
| into a matrix, one row for each day of the year. So 2000 has about
9000
| rows, analysis2000 has 366.
| The problem is in the DAY part of the function, which returns this
| error. Can anyone shed some light for me?
| Thanks in advance.
| Margo Guda.