Forum Discussion
Finding the time
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)
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.
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