Thread: time function

I am trying to make forms that employee less complicated as passable so they do not have to type in as much information. I am trying to make a formula that will take the hour of the day and return ether a 1 or a 2 that represents what shift it is according to the current time of day. Ex if hour = 19-6 then s =1 if hour = 7-18 then s = 2

PL try this formula in the required cell

=IF(OR(NOW()<=TIME(6,0,0),NOW()>=TIME(19,0,0)),1,IF(AND(NOW()<=TIME(18,0,0),NOW()>=TIME(7,0,0)),2,"" ))

Think you kvsrinivasamurthy but no matter what time I change the computer time to in bois and start menu time it will only bring up a 1. I trayed to modify yours with: IF(((NOW()<=TIME(6,0,0)),AND(NOW()>=TIME(19,0,0))),1,OR((NOW()<=TIME(18,0,0)),AND(NOW()>=TIME(7,0,0) )),2,) but it does not work either

Pl replace the formula like this.Remember that only when change happens in workd\sheet this will change.

=IF(OR(MOD(NOW(),1)<=TIME(6,0,0),MOD(NOW(),1)>=TIME(19,0,0)),1,IF(AND(MOD(NOW(),1)<=TIME(18,0,0),MOD (NOW(),1)>=TIME(7,0,0)),2,"" ))