Results 1 to 6 of 6

Thread: VLookup of date and time *RESOLVED*

  1. #1

    Thread Starter
    Frenzied Member Robbo's Avatar
    Join Date
    Jan 2001
    Location
    Bradford
    Posts
    1,143

    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

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  3. #3

    Thread Starter
    Frenzied Member Robbo's Avatar
    Join Date
    Jan 2001
    Location
    Bradford
    Posts
    1,143

    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

  4. #4

    Thread Starter
    Frenzied Member Robbo's Avatar
    Join Date
    Jan 2001
    Location
    Bradford
    Posts
    1,143

    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

  5. #5
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  6. #6

    Thread Starter
    Frenzied Member Robbo's Avatar
    Join Date
    Jan 2001
    Location
    Bradford
    Posts
    1,143

    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
  •  



Click Here to Expand Forum to Full Width