Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Referencing Time in Excel
We have discovered a problem in referencing time using the LOOKUP function and I haven't been able to resolve it using VLOOKUP or the INDEX and MATCH combination The easiest way to demonstrate the problem is to creat a list of times from 10:00 am to 12:00 pm in 15 minute increments, then copy the list into the adjacent column and convert that list into the decimal equivalent. See below for what the list should look like. I expanded the decimals to the maximum number of decimals. 10:00 0.416666666666667 10:15 0.427083333333333 10:30 0.437500000000000 10:45 0.447916666666667 11:00 0.458333333333333 11:15 0.468750000000000 11:30 0.479166666666667 11:45 0.489583333333333 12:00 0.500000000000000 Now if you write a formula using LOOKUP or VLOOKUP to try to return the decimal equivalent of a time in the left column. It works fine except at a few times like 10:45 and 11:30 when it returns the values for 10:30 and 11:15 respectively. Any help would be appreciated. Jeff -- jjhmbh ------------------------------------------------------------------------ jjhmbh's Profile: http://www.excelforum.com/member.php...o&userid=29748 View this thread: http://www.excelforum.com/showthread...hreadid=494626 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Referencing Time in Excel
Both LOOKUP and VLOOKUP work okay for me in my (maybe limited) tests.
What data are you comparing, a time string or time value, and what formula are you using. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "jjhmbh" wrote in message ... We have discovered a problem in referencing time using the LOOKUP function and I haven't been able to resolve it using VLOOKUP or the INDEX and MATCH combination The easiest way to demonstrate the problem is to creat a list of times from 10:00 am to 12:00 pm in 15 minute increments, then copy the list into the adjacent column and convert that list into the decimal equivalent. See below for what the list should look like. I expanded the decimals to the maximum number of decimals. 10:00 0.416666666666667 10:15 0.427083333333333 10:30 0.437500000000000 10:45 0.447916666666667 11:00 0.458333333333333 11:15 0.468750000000000 11:30 0.479166666666667 11:45 0.489583333333333 12:00 0.500000000000000 Now if you write a formula using LOOKUP or VLOOKUP to try to return the decimal equivalent of a time in the left column. It works fine except at a few times like 10:45 and 11:30 when it returns the values for 10:30 and 11:15 respectively. Any help would be appreciated. Jeff -- jjhmbh ------------------------------------------------------------------------ jjhmbh's Profile: http://www.excelforum.com/member.php...o&userid=29748 View this thread: http://www.excelforum.com/showthread...hreadid=494626 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Referencing Time in Excel
I am using LOOKUP and use the time value. Also I am referencing the time outside of the array. If I reference the time in the array, it returns the correct value. For instance, if you use the array below <A <B <C 11:00 1 11:30 11:15 2 11:30 3 11:45 4 12:00 5 The formula LOOKUP(A3, A1:B5) returns 3 But if you reference the value outside of the table, say in cell C1, the formula returns 2. -- jjhmbh ------------------------------------------------------------------------ jjhmbh's Profile: http://www.excelforum.com/member.php...o&userid=29748 View this thread: http://www.excelforum.com/showthread...hreadid=494626 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Referencing Time in Excel
I think you are entering something incorrectly, as it returns 3 for me.
Is A1:A5 typed in, or a formula result? I can't get 2 no matter what I have tried. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "jjhmbh" wrote in message ... I am using LOOKUP and use the time value. Also I am referencing the time outside of the array. If I reference the time in the array, it returns the correct value. For instance, if you use the array below <A <B <C 11:00 1 11:30 11:15 2 11:30 3 11:45 4 12:00 5 The formula LOOKUP(A3, A1:B5) returns 3 But if you reference the value outside of the table, say in cell C1, the formula returns 2. -- jjhmbh ------------------------------------------------------------------------ jjhmbh's Profile: http://www.excelforum.com/member.php...o&userid=29748 View this thread: http://www.excelforum.com/showthread...hreadid=494626 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help with excel referencing to outlook | Excel Discussion (Misc queries) | |||
export access to excel. change access & update excel at same time | Excel Discussion (Misc queries) | |||
Excel Range Value issue (Excel 97 Vs Excel 2003) | Excel Discussion (Misc queries) | |||
Excel Date Time to Unix Time | Excel Discussion (Misc queries) | |||
Accumulate weekly time to total time in Excel. | Excel Discussion (Misc queries) |