Forum Discussion

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

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 ce...
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)

     

     

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
    That's worked perfectly, thank you 👍
  • 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

Resources