Aug 24 2021 08:55 AM
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:00:00 and 01:59:59 returns a value of "0000 - 0200" and then value between 02:00:00 and 03:59:59 returns a value of "0200 - 0400" and so on. I've created a table on a separate worksheet and tried to use the following formula but get a "#N/A" error.
=LOOKUP(2,1/(Sheet2!$A$2:$A$13<=G4)/(Sheet2!$B$2:$B$13>=G4),Sheet2!$C$2:$C$13)
Aug 24 2021 09:06 AM
Better to have sample file or at least screenshot. If copy/paste your formula it works
Sheet2:
Aug 24 2021 09:12 AM
here are the screen captures of the data I'm working from. Thank you!
Aug 24 2021 09:47 AM
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.
Aug 24 2021 09:52 AM
Aug 24 2021 09:54 AM
Aug 24 2021 09:57 AM
Aug 24 2021 09:58 AM
If it doesn't work only for one cell - hard to say without the file.
If it doesn't work for all cells, perhaps time in column G is entered as text, not as actual time (which is number internally). You may check in any empty cell by =ISTEXT(G4)
Aug 24 2021 11:10 AM
Aug 24 2021 11:32 AM
Without 365 the formula I posted 'reduces' to
= TEXT(2*INT(HOUR(time)/2), "00") & "00" &
" -" &
TEXT(2*INT(HOUR(time)/2)+2, "00") & "00"
That doesn't help, however, if 'time' is presented as Text.
Aug 24 2021 12:08 PM
SolutionTo 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.
Aug 24 2021 12:15 PM
Aug 24 2021 12:30 PM
You are welcome, glad it helped
Aug 24 2021 12:08 PM
SolutionTo 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.