Change Color and Text

rtod2
rtod2 used Ask the Experts™
on
The cell B1 shown in the screenshot should reflect the words "Open" and the color Green when the market is Open from 8:30am to 3:00pm Central Time, and should take on the words "Closed" and the color Red when outside of those hours.  Assistance is greatly appreciated.
Open-and-Closed.png
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2012

Commented:
are you doing this in GoogleDocs?  or do you want it in Excel - the solution in Excel can determine what time zone you're already in.  Not sure Google Docs can do that.

Author

Commented:
I want it based on CT.  The GoogleClock function uses a current time zone of the viewer.  I want to force Central Time.
Most Valuable Expert 2012
Top Expert 2012

Commented:
Here's the Excel solution, using source to get the UTC time from: http://excel.tips.net/T002185_Automatically_Converting_to_GMT.html

I converted to get the GMT time, then subtrated 6 hours to get Central Standard Time, then did a simple if statement and conditional format for the coloring.

See attached,

Dave
marketStatus.xls
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Most Valuable Expert 2012
Top Expert 2012

Commented:
For Google Docs - here's the link on getting UTC for GMT and you can do math from there:

http://knol.google.com/k/google-docs-guide-2/google-docs-help-import-functions-speed/2vcnhxffa8r42/19#

Dave
Most Valuable Expert 2012
Top Expert 2012

Commented:
Are you there?  I got it to work.

I created an import on the time, then converted the resultant string to a time that I added 18 hours to (or could subtract 6 from) to get the CST.  The rest is straightforward, right?

Dave
Most Valuable Expert 2012
Top Expert 2012

Commented:
Here you go.  Its in Excel, but you can move it up to Google Docs:

After you get it there, put this command in cell B9 - its supposed to refresh hourly. and the link above will help if you want more frequent refreshes.

=importHTML("http://timeanddate.com/worldclock","table",2)

Let me know if this helps or if more assistance is required.

Enjoy!

Dave
marketStatus.xls
Most Valuable Expert 2012
Top Expert 2012
Commented:
Alternatively, you can use

=importHTML("http://timeanddate.com/worldclock","table",1) in that cell, then make the formulas point to Houston.  of course the parsing of that string is another step...

Dave

Author

Commented:
Hmmm..    Discussing B3 here >> https://spreadsheets.google.com/ccc?key=0ApGg6c9aeywQdG8yTzVWSnJPbWpFdmJJSWFBUVhERXc&hl=en&authkey=CNPC_ZkP#gid=0

I opened it for editing but don't wish to mess with any of the other values until we get this one right.  Assistance is greatly appreciated.
Most Valuable Expert 2012
Top Expert 2012

Commented:
ok - I have a few minutes - you want to chat there?

Dave

Author

Commented:
Incredible!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial