Change Color and Text
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
Open-and-Closed.png
Last Comment
dlmille
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.
ASKER
I want it based on CT. The GoogleClock function uses a current time zone of the viewer. I want to force Central Time.
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
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
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
http://knol.google.com/k/google-docs-guide-2/google-docs-help-import-functions-speed/2vcnhxffa8r42/19#
Dave
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
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
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
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
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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.
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.
ok - I have a few minutes - you want to chat there?
Dave
Dave
ASKER
Incredible!
Spreadsheets
A spreadsheet is an interactive computer application program for organization, analysis and storage of data in tabular form. Spreadsheets developed as computerized simulations of paper accounting worksheets. The program operates on data represented as cells of an array, organized in rows and columns. Each cell of the array may contain either numeric or text data, or the results of formulas that automatically calculate and display a value based on the contents of other cells.
7K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
