Forum Discussion
Solved
vlookup between two times and return a corresponding value
I am trying to lookup values between two values to return a corresponding result. I have a time value of 00:00:23 in cell G4 and want to create a formula to make it so that any time value between 00...
To convert text to time it's not enough to change format, you need to re-enter the values. Easiest way - apply Time format to column G; select it; Data->Text to Columns-> select Fixed Width->Finish.
I have tried a different strategy using 365
= LET(z, "00",
lower, 2*INT(HOUR(time)/2),
upper, lower+2,
uTxt, TEXT(upper, z) & z,
lTxt, TEXT(lower, z) & z,
lTxt & " -" & uTxt
)The idea to to calculate the time range directly, rather than using a lookup.
I have no idea how to do that...