and formula with 4 different scenarios

[Ricardo9211]

Hello,
i am having difficulty with trying to write a specific formula with ANDs can you tell me if this can be done
i have a now() cell in A2 and need in D2 to say that
if time is between 9am and 12:59pm the show "10:00 AM"
or if time is between 1pm and 4:59pm show "2pM
or if time is between 5pm and 8:59pm show 6pm
or if time is between 9pm to 11:59pm show 10pm

can this be done in one cell of do i need 4 different cells

[martindwilson]

=LOOKUP(A2,{0.375,0.5416666667,0.7083333333,0.875},{0.416666667,0.583333333,0.75,0.916666667}) cell formated time hh:mm . is a way of doing it

[shg]

Or, for the decimal-impaired,

=LOOKUP(MOD(A2, 1), {9,13,17,21}/24, {10,14,18,22}/24)

[martindwilson]

lol, i knew there must be a simpler way