Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combine Date & Time Cells | Excel Discussion (Misc queries) | |||
change date based on time | Excel Discussion (Misc queries) | |||
How to calculate Date & Time differences | Excel Worksheet Functions | |||
Using VLOOKUP with a Date and Time | Excel Discussion (Misc queries) | |||
Imported Date & Time format with calcs. managed in excel from imrp | Excel Worksheet Functions |