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