Support Board
Date/Time: Thu, 13 Feb 2025 13:44:39 +0000
Spreadsheet programming question
View Count: 795
| [2020-12-08 06:54:23] |
| User337551 - Posts: 7 |
|
How could I determine how far along in a time bar? example: using a 5 minute chart, how would I determine how many seconds into the bar has passed... been trying different formulas with fractime, mround... cant quite figure it out. thanks tim
|
| [2020-12-08 15:28:48] |
| John - SC Support - Posts: 37916 |
|
You would need to use the NOW() function to get the current time and then subtract the current bars start time from it. So you could use a formula like the following to get the number of seconds into the bar: =(NOW() - A3)*24*60*60 For the most reliable, advanced, and zero cost futures order routing, use the Teton service: Sierra Chart Teton Futures Order Routing |
| [2020-12-08 17:38:35] |
| User337551 - Posts: 7 |
|
i tried this... the cell value is 2202057.3927376186. im still lost
|
| [2020-12-08 19:19:11] |
| John - SC Support - Posts: 37916 |
|
You need to do this on a Spreadsheet Study so that you have the bar information. In the Spreadsheet Study the A column is the Date and Time of the bars. Refer to the information here for the Spreadsheet Study: Overview of Spreadsheet Studies: Introduction to Spreadsheet Studies For the most reliable, advanced, and zero cost futures order routing, use the Teton service: Sierra Chart Teton Futures Order Routing |
| [2020-12-08 19:28:26] |
| User337551 - Posts: 7 |
|
im working in a spreadsheet study, i have the bar information, and ive put the formula you recommended in a cell... 220257.3927376186 for a result. what does this number represent
|
| [2020-12-08 19:58:10] |
| John - SC Support - Posts: 37916 |
|
It represents the Date and Time in a decimal format by specifying the number of days since December 30, 1899. This is specified here: Spreadsheet Functions: Serial DateTime Values Since you are subtracting the start of the bar from the current time, you should only have the number of seconds since the open of the bar. What cell are you entering the information into? We are expecting that you would be entering the formula into K3. Then K3 will continuously update to give you the time since the beginning of the bar. The rest of the K column can be ignored. If the issue isn't about the cell you are entering the values into, then just try putting in the following formula and make sure you are just getting back the numbers after the decimal: = NOW() - A3 For the most reliable, advanced, and zero cost futures order routing, use the Teton service: Sierra Chart Teton Futures Order Routing |
| [2020-12-08 20:56:26] |
| User337551 - Posts: 7 |
|
i still dont understand it... thanks anyway
|
| [2020-12-09 16:22:29] |
| Sawtooth - Posts: 4173 |
|
how would I determine how many seconds into the bar has passed?
Try this:=ROUND(($J$41-$A$3)*86400,0) This subtracts the current chart time from the bar's start time, then multiplies it by the number of seconds in 24 hours, then it is rounded to the nearest whole second. In the SerialDateTime format, the integer is the number of days since 1/1/1900, and the decimal is the fraction of the day. If you want to display this value, put the formula in row 3 of a Formula Column, and use the Text Display for Study study, referencing the spreadsheet study column/subgraph. |
To post a message in this thread, you need to log in with your Sierra Chart account: