Forum Discussion

JoeEason92's avatar
JoeEason92
Copper Contributor
Aug 16, 2023

Finding the time

I have a list of timecodes next to their allocated times and use the =NOW() function to have a 'live' time in cell A3. For simplicities sake let's just say column B has time codes A, B, C and D in cells B1-B4 and column C has time steps of 0900, 1000, 1100 and 1200 in cells C1-C4. I want to be able to find the time which is closest to, but not later than the current time from my database.

I couldn't get it to return the adjacent cell in column B so I tried using =INDEX(C1:C4, MATCH(TRUE, C1:C4 > A3, 0)) to just return the closest time but this returns a #N/A error. Is there a specific formatting issue with using time in formulas or am I going about this the wrong way?

Any help would be appreciated

Excel
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Aug 16, 2023

    JoeEason92 Something like this perhaps?

    First you need to enter the times in C so that Excel recognizes them as a time value "hh:mm".

    Then, be aware that NOW() returns a date and time value. You only want to use the time portion. That's done by MOD(A3,1)

    Using the > operator will find 13:00 as the first time greater than 12:39. since you want the latest time that is NOT greater than the current time, just deduct 1 at the end.

    =INDEX(B1:B4, MATCH(TRUE, C1:C4 > MOD(A3,1), 0)-1)

     

     

  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Aug 16, 2023

    JoeEason92 

    Please clarify "column C has time steps of 0900, 1000, 1100 and 1200", are they texts or numbers? If numbers why leading zero is here? And are they seconds, minutes or what?

    Better to have sample file or at least screenshot of what do you have.

    • JoeEason92's avatar
      JoeEason92
      Copper Contributor
      Aug 16, 2023

      SergeiBaklan apologies, the times are taken from a separate sheet so they're populated, for example, as =Sheet2!A5 but the cell formatting is a time format and shows them as 09:00:00 I'm just away from my computer at the moment but I'll see if I can get a screenshot when I get back in

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Aug 16, 2023

    JoeEason92 Something like this perhaps?

    First you need to enter the times in C so that Excel recognizes them as a time value "hh:mm".

    Then, be aware that NOW() returns a date and time value. You only want to use the time portion. That's done by MOD(A3,1)

    Using the > operator will find 13:00 as the first time greater than 12:39. since you want the latest time that is NOT greater than the current time, just deduct 1 at the end.

    =INDEX(B1:B4, MATCH(TRUE, C1:C4 > MOD(A3,1), 0)-1)

     

     

    • JoeEason92's avatar
      JoeEason92
      Copper Contributor
      Aug 16, 2023
      Ah yeah, that could be the issue. I tested against NOW() with an =IF(A3>C1, True, False) and =IF(A3<C4, True, False) and just assumed they were the same formats for the formula when they worked. I didn't realise tge date would affect it. I'll give it a go shortly and see if it works

Share

Resources