Formula for Time

[Ricardo9211]

I am trying to get a formula that will state specific data if within a time frame

I have cell A20 as =NOW()

Then have A21 as =IF(A20<TIME(13,50,0),"10:00 AM",IF(AND(A20>TIME(13,51,0),A20<TIME(17,50,0)),"2:00 PM",IF(AND(A20>TIME(17,51,0),A20<TIME(21,50,0)),"6:00 PM","10:00 PM")))

for some reason this will not work can someone tell me what i am doing wrong or point me in the right direction


Thanks

[daddylonglegs]

Hello Ricardo,

=NOW() contains the date as well as time and therefore will always be greater than all of your times, hence the default value of 10:00 PM is always returned.

Try making A20 just the current time, i.e.

=MOD(NOW(),1)

then your formula will work in most cases....although I note that if the current time is 13:50 it will also return 10:00 PM because you haven't closed off all the time ranges.....you could try this version with LOOKUP

=LOOKUP(A20,{0,"13:50","17:50","21:50"}+0,{"10:00 AM","2:00 PM","6:00 PM","10:00 PM"})

[Ricardo9211]

That worked pefectly both ways i ended up closing the AM time with 12:01am so both worked but i definately liked yours better


Thanks alot

now the powers that be have me on bigger badder things