-
Sep 24th, 2020, 08:30 AM
#1
Thread Starter
Frenzied Member
VLookup of date and time *RESOLVED*
wondering if any one can help the best way to do this
23.09.2020
24.09.2020 JR 16484 24.09.2020 14:08:26
25.09.2021 24.09.2020 14:08:26
so formula for number is
=VLOOKUP(A385&" 22:08:27",Jumbo1!G:H,2,TRUE)
and looks up from another sheet
Date Time Date / Time Value
84
23.09.2020 23:08:34 23.09.2020 23:08:34 152
24.09.2020 00:08:34 24.09.2020 00:08:34 152
24.09.2020 01:08:33 24.09.2020 01:08:33 152
24.09.2020 02:08:33 24.09.2020 02:08:33 152
24.09.2020 03:08:33 24.09.2020 03:08:33 152
24.09.2020 04:08:32 24.09.2020 04:08:32 152
24.09.2020 05:08:31 24.09.2020 05:08:31 0;1;4
24.09.2020 06:08:31 24.09.2020 06:08:31 0;1;4
24.09.2020 07:08:30 24.09.2020 07:08:30 1384
24.09.2020 08:08:29 24.09.2020 08:08:29 3763
24.09.2020 09:08:29 24.09.2020 09:08:29 6323
24.09.2020 10:08:28 24.09.2020 10:08:28 8020
24.09.2020 11:08:28 24.09.2020 11:08:28 10240
24.09.2020 12:08:27 24.09.2020 12:08:27 12876
24.09.2020 13:08:27 24.09.2020 13:08:27 14357
24.09.2020 14:08:26 24.09.2020 14:08:26 16484
so want it to lookup the date and the time nearest to 10pm thus giving the 16484 for the 24/09
any idea how i should do this properly its just picking up the last one at present
thanks in advance
Last edited by Robbo; Sep 25th, 2020 at 07:39 AM.
-----------------------------------------------
"The hall is rented,"
"the orchestra is engaged,"
"its now time to see if you can dance!"
Q, Q-Who, Star Trek The Next Generation
-----------------------------------------------
General Work day
-----------------------------------------------
DOS, Win 95, Win 98 SE, Win ME, Win NT 4.0 SP6a, Windows 2000 SP3, Window XP SP1, Windows 7, Windows 8/8.1, Windows 10, Office 97 Pro, Office 2000 Pro, Office 2010, Office 2013, Office 2016, Office 2019, Visual Basic 6 (SP5), SQL, Oracle
-
Sep 25th, 2020, 05:11 AM
#2
Re: VLookup of date and time
this seems to work correctly, it will pick up the one before the time looked up, not one after the time, so while 2pm would be closer to 14:08, vlookup would find the 13:08, if you need to find the latter you would need to compare the 2 values to find which is closer
please give more detail, with scenarios of values looked up and desire result to be found
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Sep 25th, 2020, 06:00 AM
#3
Thread Starter
Frenzied Member
Re: VLookup of date and time
Hiya
doesn't quite work correctly its just showing me the last entry
=VLOOKUP(A386&" 22:0",Jumbo1!G:H,2)
DB_FLOW_PARA.s_ProcessValues.r_Totalizer_1;"24.09.2020 20:08:23";28997
DB_FLOW_PARA.s_ProcessValues.r_Totalizer_1;"24.09.2020 21:08:23";31125
DB_FLOW_PARA.s_ProcessValues.r_Totalizer_1;"24.09.2020 22:08:22";31611
DB_FLOW_PARA.s_ProcessValues.r_Totalizer_1;"24.09.2020 23:08:21";31611
DB_FLOW_PARA.s_ProcessValues.r_Totalizer_1;"25.09.2020 00:08:21";31611
DB_FLOW_PARA.s_ProcessValues.r_Totalizer_1;"25.09.2020 01:08:21";31611
DB_FLOW_PARA.s_ProcessValues.r_Totalizer_1;"25.09.2020 02:08:20";31611
DB_FLOW_PARA.s_ProcessValues.r_Totalizer_1;"25.09.2020 03:08:19";31611
DB_FLOW_PARA.s_ProcessValues.r_Totalizer_1;"25.09.2020 04:08:19";31611
DB_FLOW_PARA.s_ProcessValues.r_Totalizer_1;"25.09.2020 05:08:18";0;1;44099214100
DB_FLOW_PARA.s_ProcessValues.r_Totalizer_1;"25.09.2020 06:08:18";1665
DB_FLOW_PARA.s_ProcessValues.r_Totalizer_1;"25.09.2020 07:08:17";4186
DB_FLOW_PARA.s_ProcessValues.r_Totalizer_1;"25.09.2020 08:08:16";6351
DB_FLOW_PARA.s_ProcessValues.r_Totalizer_1;"25.09.2020 09:08:16";8815
DB_FLOW_PARA.s_ProcessValues.r_Totalizer_1;"25.09.2020 10:08:15";11130
and its showing me 11130 so doesn't even seem to pick date up
main sheet 25/09/2020
lookup sheet =MID(Data_log_10[@Column1],45,10) = 25.09.2020
but i cant change the main sheet!
thanks in advance
-----------------------------------------------
"The hall is rented,"
"the orchestra is engaged,"
"its now time to see if you can dance!"
Q, Q-Who, Star Trek The Next Generation
-----------------------------------------------
General Work day
-----------------------------------------------
DOS, Win 95, Win 98 SE, Win ME, Win NT 4.0 SP6a, Windows 2000 SP3, Window XP SP1, Windows 7, Windows 8/8.1, Windows 10, Office 97 Pro, Office 2000 Pro, Office 2010, Office 2013, Office 2016, Office 2019, Visual Basic 6 (SP5), SQL, Oracle
-
Sep 25th, 2020, 06:15 AM
#4
Thread Starter
Frenzied Member
Re: VLookup of date and time
think have resolved it
had to change date formula to
=DATE(MID(Data_log_10[@Column1],51,4),MID(Data_log_10[@Column1],48,2),MID(Data_log_10[@Column1],45,2))
and seems to pickup correctly now
-----------------------------------------------
"The hall is rented,"
"the orchestra is engaged,"
"its now time to see if you can dance!"
Q, Q-Who, Star Trek The Next Generation
-----------------------------------------------
General Work day
-----------------------------------------------
DOS, Win 95, Win 98 SE, Win ME, Win NT 4.0 SP6a, Windows 2000 SP3, Window XP SP1, Windows 7, Windows 8/8.1, Windows 10, Office 97 Pro, Office 2000 Pro, Office 2010, Office 2013, Office 2016, Office 2019, Visual Basic 6 (SP5), SQL, Oracle
-
Sep 25th, 2020, 07:01 AM
#5
Re: VLookup of date and time
=VLOOKUP(A386&" 22:0",Jumbo1!G:H,2)
i assume that a396 contains 25.09.2020, so the returned lookup is correct closest value below the lookup time, what do you want it to find in this case?
the values here are all text, no dates, unless your system locale allows different date conversions to mine, the dates that are being lookedup are not able to be converted to valid dates without multi step conversion
=VLOOKUP(A386&" 09:0",Jumbo1!G:H,2)
if i change the hour to 09 then it returns 6351 as expected
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Sep 25th, 2020, 07:35 AM
#6
Thread Starter
Frenzied Member
Re: VLookup of date and time
yes if you change the time now its working was just the format of the date which wasn't working correctly
cheers
-----------------------------------------------
"The hall is rented,"
"the orchestra is engaged,"
"its now time to see if you can dance!"
Q, Q-Who, Star Trek The Next Generation
-----------------------------------------------
General Work day
-----------------------------------------------
DOS, Win 95, Win 98 SE, Win ME, Win NT 4.0 SP6a, Windows 2000 SP3, Window XP SP1, Windows 7, Windows 8/8.1, Windows 10, Office 97 Pro, Office 2000 Pro, Office 2010, Office 2013, Office 2016, Office 2019, Visual Basic 6 (SP5), SQL, Oracle
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|