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

     

     

SergeiBaklan's avatar
SergeiBaklan
MVP

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

Resources