Using time functions to give a warning

  • Thread starter Thread starter Colin Hayes
  • Start date Start date
C

Colin Hayes

Hi

In cell I14 I need to put a formula which says 'Open' when the PC clock
is between 8 am and 4.30 pm , and 'Closed' when the PC clock is later
than 4.30 pm.

Can someone help with the code , please?

thanks.
 
Hi,

Try this

=if(and(time(8,0,0)<=now(),time(16,30,0)>=now()),"Open","Closed").

This will not auto update. You will have to press F9 to update

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
Hi Colin
Not sure if that's what you want but try it.
=IF(AND(NOW()>TIME(8,0,0),NOW()<TIME(16,30,0)),"Open","Close")
Make sure your cells are formatted as time.
Regards
John
 
Try this:

=IF(AND(mod(NOW(),1)>=TIME(8,0,0),MOD(NOW(),1)<=TIME
(16,30,0)),"Open","Closed")

Hope this helps.

Pete
 
Hi All

OK thanks for your help and expertise on this.

I'm using this formula to say 'Open' between 8 am and 4.30 pm , and
'Closed' after this.

=IF(AND(MOD(NOW(),1)>=TIME(8,0,0),MOD(NOW(),1)<=TIME(16,30,0)),"Open","Closed")


It works fine. As a refinement , is it possible to modify the formula to
only pertain to weekdays , and say 'Closed' at weekends?

Grateful for any advice.



Best Wishes
 
Try this variation, Colin:

=IF(WEEKDAY(NOW(),2)>5,"Closed",IF(AND(MOD(NOW(),1)>=TIME(8,0,0),MOD
(NOW(),1)<=TIME(16,30,0)),"Open","Clos­ed"))

Hope this helps.

Pete
 
Try this variation, Colin:

=IF(WEEKDAY(NOW(),2)>5,"Closed",IF(AND(MOD(NOW(),1)>=TIME(8,0,0),MOD
(NOW(),1)<=TIME(16,30,0)),"Open","Clos­ed"))

Hope this helps.

Pete
Click to expand...


HI Pete

OK Thanks for that - it's working brilliantly.



Best Wishes
 
Back
Top