| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refreshing of =now() not producing what I need
I have found a macro that looks to refresh itself every second. A1 is =now() I have the open and close times for a particular market. Lets say it opens at 7:00 am (location is C19) and closed at 3:30 pm (location is C20) When I do the following: =if(and($A$1C19,$A$1<C20),”Market is open”,”Market is closed”) It does not work. I even try to break it into 2 parts. And it still does not seem to work. Any ideas? -- sungen99 ------------------------------------------------------------------------ sungen99's Profile: http://www.excelforum.com/member.php...fo&userid=9144 View this thread: http://www.excelforum.com/showthread...hreadid=526117 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Refreshing of =now() not producing what I need
does anyone know a good link or links to time based macros/formulas? thanks -- sungen99 ------------------------------------------------------------------------ sungen99's Profile: http://www.excelforum.com/member.php...fo&userid=9144 View this thread: http://www.excelforum.com/showthread...hreadid=526117 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Refreshing of =now() not producing what I need
Remember that NOW() returns the date as well as the time.
Try =MOD(NOW(),1) -- Gary''s Student "sungen99" wrote: I have found a macro that looks to refresh itself every second. A1 is =now() I have the open and close times for a particular market. Lets say it opens at 7:00 am (location is C19) and closed at 3:30 pm (location is C20) When I do the following: =if(and($A$1C19,$A$1<C20),€¯Market is open€¯,€¯Market is closed€¯) It does not work. I even try to break it into 2 parts. And it still does not seem to work. Any ideas? -- sungen99 ------------------------------------------------------------------------ sungen99's Profile: http://www.excelforum.com/member.php...fo&userid=9144 View this thread: http://www.excelforum.com/showthread...hreadid=526117 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Refreshing of =now() not producing what I need
=if(and($A$1today()+C19,$A$1<today()+C20),€¯Mark et is open€¯,€¯Market is
closed€¯) -- Regards, Tom Ogilvy "sungen99" wrote: I have found a macro that looks to refresh itself every second. A1 is =now() I have the open and close times for a particular market. Lets say it opens at 7:00 am (location is C19) and closed at 3:30 pm (location is C20) When I do the following: =if(and($A$1C19,$A$1<C20),€¯Market is open€¯,€¯Market is closed€¯) It does not work. I even try to break it into 2 parts. And it still does not seem to work. Any ideas? -- sungen99 ------------------------------------------------------------------------ sungen99's Profile: http://www.excelforum.com/member.php...fo&userid=9144 View this thread: http://www.excelforum.com/showthread...hreadid=526117 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Refreshing of =now() not producing what I need
works! great. thank you all!!! -- sungen99 ------------------------------------------------------------------------ sungen99's Profile: http://www.excelforum.com/member.php...fo&userid=9144 View this thread: http://www.excelforum.com/showthread...hreadid=526117 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Refreshing of =now() not producing what I need
how would i do this? d5= the close time. a1 = current time i want k5 to be how much time is left IF its still open. so basically is would say 1:25:11 for 1 hour, 25 minutes and 11 seconds LEFT to trade. OR is would say CLOSED. -- sungen99 ------------------------------------------------------------------------ sungen99's Profile: http://www.excelforum.com/member.php...fo&userid=9144 View this thread: http://www.excelforum.com/showthread...hreadid=526117 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Refreshing of =now() not producing what I need
=if(and($A$1today()+C19,$A$1<today()+C20),(Today( )+C20)-A1,"Closed")
format the cell as a time value. This assumes C20 holds the closing time as before. If you want to use D5, then change the formula. I also check the opening time for completeness. If you changed A1 to be =Mod(now(),1) then it would be =if(and($A$1C19,$A$1<C20),C20-A1"Closed") -- Regards, Tom Ogilvy "sungen99" wrote: how would i do this? d5= the close time. a1 = current time i want k5 to be how much time is left IF its still open. so basically is would say 1:25:11 for 1 hour, 25 minutes and 11 seconds LEFT to trade. OR is would say CLOSED. -- sungen99 ------------------------------------------------------------------------ sungen99's Profile: http://www.excelforum.com/member.php...fo&userid=9144 View this thread: http://www.excelforum.com/showthread...hreadid=526117 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Refreshing of =now() not producing what I need
I love how this is working. It's really nice. One last request. I have added conditional formatting to this now too. If the cell is = to closed its orange, if its not = to closed its green. How would I add if there is 10 minutes or less to make it red???? thanks Im using this for the formula btw. =IF(AND($A$1C6,$A$1<D6),D6-$A$1,"Closed") -- sungen99 ------------------------------------------------------------------------ sungen99's Profile: http://www.excelforum.com/member.php...fo&userid=9144 View this thread: http://www.excelforum.com/showthread...hreadid=526117 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Refreshing of =now() not producing what I need
Cell value is Less than or Equal to =TimeValue("00:10")
-- Regards, Tom Ogilvy "sungen99" wrote in message ... I love how this is working. It's really nice. One last request. I have added conditional formatting to this now too. If the cell is = to closed its orange, if its not = to closed its green. How would I add if there is 10 minutes or less to make it red???? thanks Im using this for the formula btw. =IF(AND($A$1C6,$A$1<D6),D6-$A$1,"Closed") -- sungen99 ------------------------------------------------------------------------ sungen99's Profile: http://www.excelforum.com/member.php...fo&userid=9144 View this thread: http://www.excelforum.com/showthread...hreadid=526117 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Refreshing of =now() not producing what I need
Just to add, that should preceed the condition that sets it to green.
-- Regards, Tom Ogilvy "sungen99" wrote in message ... I love how this is working. It's really nice. One last request. I have added conditional formatting to this now too. If the cell is = to closed its orange, if its not = to closed its green. How would I add if there is 10 minutes or less to make it red???? thanks Im using this for the formula btw. =IF(AND($A$1C6,$A$1<D6),D6-$A$1,"Closed") -- sungen99 ------------------------------------------------------------------------ sungen99's Profile: http://www.excelforum.com/member.php...fo&userid=9144 View this thread: http://www.excelforum.com/showthread...hreadid=526117 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Refreshing of =now() not producing what I need
It does not seem to work. I have cell G3 have how long before the clos you want to the color to change to red. If I make it 00:10:00 its no giving me 10 minutes, its giving me 12:10:00 AM how do I change this -- sungen9 ----------------------------------------------------------------------- sungen99's Profile: http://www.excelforum.com/member.php...nfo&userid=914 View this thread: http://www.excelforum.com/showthread.php?threadid=52611 |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Refreshing of =now() not producing what I need
Selfish bump. : -- sungen9 ----------------------------------------------------------------------- sungen99's Profile: http://www.excelforum.com/member.php...nfo&userid=914 View this thread: http://www.excelforum.com/showthread.php?threadid=52611 |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Refreshing of =now() not producing what I need
Sorry I dont seem to be able to figure out how to make this work. :( -- sungen99 ------------------------------------------------------------------------ sungen99's Profile: http://www.excelforum.com/member.php...fo&userid=9144 View this thread: http://www.excelforum.com/showthread...hreadid=526117 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| CONCATENATE not producing any results | Excel Worksheet Functions | |||
| Help for producing a calculator | Excel Discussion (Misc queries) | |||
| sumproduct producing #VALUE! | Excel Worksheet Functions | |||
| UDF producing #NAME? | Excel Discussion (Misc queries) | |||
| Help With Producing Results Please | Excel Programming | |||
![sungen99[_76_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
