Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup problem with Date Time
Hi
Anyone know how to fix this problem? I have SHEET1 with a date/time in cell A1 and then a formula to add one hour to the cell from the row above (for cell A4 the formula is =+A3+(TIME(1,0,0)) to generate a colum of date/times that are 1 hour increments. On SHEET2 I start with the same date/time in cell A1 (from A1 on SHEET1) and add several hourly values to it, several times. For example row 5 contains; cell A5 contains "+G4" cell B5 contains "=+A5+TIME(J5,0,0)" where J5 contains 0 cell C5 contains "=+B5+TIME(K5,0,0)" where K5 contains 1 cell D5 contains "=+C5+TIME(L5,0,0)" where L5 contains 2 cell E5 contains "=+D5+TIME(M5,0,0)" where M5 contains 6 cell F5 contains "=+E5+TIME(N5,0,0)" Where N5 contains 10 cell G5 contains "=+F5+TIME(O5,0,0)" where O5 contains 8 The next row references G from the previous row. So; cell A6 contains "+G5" and so on. The problem is that if I use Vlookup or Index/Match to search column B on SHEET2 to match a value from column A on SHEET1, I can't get an exact match. =INDEX(SHEET2!$B$1:$I$501,(MATCH(A30,SHEET2!$B$1:$ B$501,0)),8) If I actually type the date/time (that is calculated and displayed in cell A30) into A30, (type "1/1/06 10:00 AM" into A30) the correct result is returned from the Index/Match. I tried changing the Match_type to 1 but then every date/time from SHEET1 returned a match if it was greater than the date/time in SHEET2 but less than the next day. Thanks Norma |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help: convert seconds to date and time | Excel Discussion (Misc queries) | |||
Imported Date & Time format with calcs. managed in excel from imrp | Excel Worksheet Functions | |||
Login Logout Date Problem | Excel Worksheet Functions | |||
Problem With Date and Time Picker Control 6.0 | Excel Discussion (Misc queries) | |||
Date Math Problem | Excel Worksheet Functions |