| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using time functions to give a warning
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using time functions to give a warning
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 "Colin Hayes" wrote in message ... 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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using time functions to give a warning
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)),"Op en","Close") Make sure your cells are formatted as time. Regards John "Colin Hayes" wrote in message ... 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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using time functions to give a warning
Try this:
=IF(AND(mod(NOW(),1)=TIME(8,0,0),MOD(NOW(),1)<=TI ME (16,30,0)),"Open","Closed") Hope this helps. Pete On Jan 11, 10:29*pm, Colin Hayes wrote: 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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using time functions to give a warning
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)<=TI ME(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 In article , Pete_UK writes Try this: =IF(AND(mod(NOW(),1)=TIME(8,0,0),MOD(NOW(),1)<=T IME (16,30,0)),"Open","Closed") Hope this helps. Pete On Jan 11, 10:29*pm, Colin Hayes wrote: 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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using time functions to give a warning
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 On Jan 14, 1:39*am, Colin Hayes wrote: 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)<=TI ME(16,30,0)),"Open","Clos*ed") 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 In article , Pete_UK writes Try this: =IF(AND(mod(NOW(),1)=TIME(8,0,0),MOD(NOW(),1)<=T IME (16,30,0)),"Open","Closed") Hope this helps. Pete On Jan 11, 10:29 pm, Colin Hayes wrote: 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.- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using time functions to give a warning
In article
, Pete_UK writes 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 HI Pete OK Thanks for that - it's working brilliantly. Best Wishes On Jan 14, 1:39*am, Colin Hayes wrote: 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)<=TI ME(16,30,0)),"Open" ,"Clos*ed") 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 In article , Pete_UK writes Try this: =IF(AND(mod(NOW(),1)=TIME(8,0,0),MOD(NOW(),1)<=T IME (16,30,0)),"Open","Closed") Hope this helps. Pete On Jan 11, 10:29 pm, Colin Hayes wrote: 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.- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| can anyone give me all the functions in the excel worksheet with | Excel Worksheet Functions | |||
| Reset should give a warning before resetting custom colors. | Setting up and Configuration of Excel | |||
| have a cell give warning | Excel Discussion (Misc queries) | |||
| Warning! Time has expired.... | Excel Worksheet Functions | |||
| How do I give permission for certain functions in excel in locked. | Excel Worksheet Functions | |||
Linear Mode
