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

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

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

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

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

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

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

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

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

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

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

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

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