Support Board
Date/Time: Wed, 30 Apr 2025 07:36:34 +0000
FRACTIME and TIME issue or problem in spreadsheets?
View Count: 52
| [2025-04-27 17:56:31] |
| user_xyz - Posts: 475 |
|
I was running a 'quick' test and naturally selected a TIME that didn't work so spent hours only to find out this formula or the data has some quirks that yields inconsistent return values. Chart with 1 min candles so A3 = 2025-04-25 13:57:00, etc. =IF(FRACTIME(A3)=TIME(13,57,00), 1,0) -> Returns 0 =IF(FRACTIME(A4)=TIME(13,56,00), 1,0) -> Returns 1 (as expected) =IF(FRACTIME(A5)=TIME(13,55,00), 1,0) -> Returns 1 (as expected) =IF(FRACTIME(A127)=TIME(11,55,00), 1,0) -> Returns 0 Thought maybe related to milliseconds but helper column shows milliseconds at 0.000 I started with 11,55,00 which Returned 0 and led me down this path. Also 12,55,00 and 13,55,00 Return 1 as expected. Date Time Of Last Edit: 2025-04-27 18:26:20
|
 20250427_104211.png / V - Attached On 2025-04-27 17:43:15 UTC - Size: 23.94 KB - 13 views |
| [2025-04-27 18:24:32] |
| user_xyz - Posts: 475 |
|
I should mention in the examples above when 0, if I SUM all values in column it is 0. Odd. (13,57,00) -> Sum of thousands of rows = 0 (13,56,00) -> Sum of thousands of rows = 397 (13,56,00) -> Sum of thousands of rows = 397 (11,55,00) -> Sum of thousands of rows = 0 Date Time Of Last Edit: 2025-04-27 18:28:05
|
| [2025-04-28 01:16:15] |
| Sawtooth - Posts: 4210 |
|
You are seeing floating point imprecision. Round down the FRACTIME to a 1 second multiple using the FLOOR function: This will produce TRUE: =FLOOR(FRACTIME(A3),1/86400)=TIME(13,57,00) |
| [2025-04-28 08:05:06] |
| user_xyz - Posts: 475 |
|
That worked thanks!
|
To post a message in this thread, you need to log in with your Sierra Chart account: