Time intervalls with a defined time start time each day

[wardzynski]

I need a column inside a straight table, that can based on the EntryTime of the trade, tell me the starting time of the candle bar where the trade occurs.

Example: 8 minute bars, start counting bars from 3:30 PM

Trade:         Entry Time:                             Starting time of Bar:

1                 4/4/2017 5:04:00 PM               4:58 PM

2                 4/18/2017 8:16:06 PM             8:10 PM

My session starts at 3:30PM (Field value), and based on the time-frame (8 minutes, 15 minutes, etc, also field value) it should tell you the start-time of the Bar that the trade belongs to.

For 8 min time-frame: First bar starts at 3:30 PM, then next at 3:38 PM, 3:46 PM, 3:54 PM,etc...

For 10 min time-frame: First bar starts at 3:30 PM, next 3:40 PM, 3:50 PM, etc..

For 13 min time-frame: First bar starts at 3:30 PM

Time-frame value comes from Time-frame field (8 min, 9 min, etc...)

Session start time comes from session field (3:30 PM, 8:30 AM, etc...)

This is currently how I have tried scripting it inside the straight table chart:

=time(floor(EntryTimeII,8/24/60)+maketime(0,2,0),'hh:mm:ss TT')

But it is not correct.

[sunny_talwar]

I am not sure I understand what you are trying to do here? Do you have a working sample you can share where we can dig deeper?

[wardzynski]

Hi Sunny,

Imagine a starting time 3:30 PM, and that I want to split this in 8 min time-intervals, so we have abstract intervals starting with [3:30 PM, 3:38 PM, 3:46 PM, 3:54 PM, 4:02 PM, 4:10 PM, 4:18 PM, 4:26 PM: 4:34 PM, 4:42 PM, 4:50 PM, 4:58 PM, 5:06 PM, 5:14 PM, 5:22 PM, 5:30 PM, 5:38 PM, etc......]

Now, what I want is to see in which interval each of the timestamps (column: StartTime) belongs to showing in the column [WHAT I WANT]. The starting time (3:30 PM in this example) and time-frame (8 mins in this example) are defined in a variable or field).

How do I make the column "What I want" ?

StartTime	WHAT I WANT
3/1/2017 4:20:47 PM	04:18:00 PM
3/3/2017 3:54:00 PM	03:54:00 PM
3/3/2017 4:34:35 PM	04:34:00 PM
3/3/2017 5:28:21 PM	05:30:00 PM
3/3/2017 5:30:45 PM	05:30:00 PM
3/7/2017 4:13:04 PM	04:10:00 PM
3/7/2017 4:50:03 PM	04:50:00 PM
3/7/2017 5:02:31 PM	04:58:00 PM
3/7/2017 5:06:02 PM	05:06:00 PM
3/7/2017 5:17:09 PM	05:14:00 PM
3/7/2017 5:46:05 PM	05:46:00 PM
3/10/2017 4:02:17 PM	04:02:00 PM
3/10/2017 4:58:51 PM	04:58:00 PM
3/14/2017 4:09:01 PM	04:10:00 PM
3/14/2017 5:50:52 PM	05:46:00 PM
3/21/2017 4:31:01 PM	04:26:00 PM
3/21/2017 5:19:34 PM	05:14:00 PM
3/21/2017 5:35:36 PM	05:30:00 PM
3/22/2017 4:14:00 PM	04:10:00 PM
3/22/2017 4:31:17 PM	04:26:00 PM
3/22/2017 4:41:00 PM	04:42:00 PM
3/22/2017 4:54:32 PM	04:50:00 PM
3/22/2017 5:04:13 PM	05:06:00 PM
3/22/2017 5:12:10 PM	05:14:00 PM
3/22/2017 5:18:48 PM	05:14:00 PM
3/22/2017 5:30:35 PM	05:30:00 PM

[sunny_talwar · Accepted solution]

Try this

Table:

LOAD RowNo() as RowNum,

*,

Time(MakeTime(15, 30) + Floor(Frac(StartTime - MakeTime(15, 29, 59)), 1/180)) as Time;

LOAD * INLINE [

    StartTime

    3/1/2017 4:20:47 PM

    3/3/2017 3:54:00 PM

    3/3/2017 4:34:35 PM

    3/3/2017 5:28:21 PM

    3/3/2017 5:30:45 PM

    3/7/2017 4:13:04 PM

    3/7/2017 4:50:03 PM

    3/7/2017 5:02:31 PM

    3/7/2017 5:06:02 PM

    3/7/2017 5:17:09 PM

    3/7/2017 5:46:05 PM

    3/10/2017 4:02:17 PM

    3/10/2017 4:58:51 PM

    3/14/2017 4:09:01 PM

    3/14/2017 5:50:52 PM

    3/21/2017 4:31:01 PM

    3/21/2017 5:19:34 PM

    3/21/2017 5:35:36 PM

    3/22/2017 4:14:00 PM

    3/22/2017 4:31:17 PM

    3/22/2017 4:41:00 PM

    3/22/2017 4:54:32 PM

    3/22/2017 5:04:13 PM

    3/22/2017 5:12:10 PM

    3/22/2017 5:18:48 PM

    3/22/2017 5:30:35 PM

];

[sunny_talwar]

Are you rounding down or rounding up or just rounding to 8 minutes?

[wardzynski]

Hi Sunny,

I am rounding down to nearest starting point and I think it is working correct right now, still testing. The only problem is that I can not sort a second column now that includes the StartTime. The interactive sort wont show correct results.

My 2nd column uses the formula:

=time(StartTime,'mm:ss')-time([Start of Bar],'mm:ss')

[StartTime]	Start of Bar	2nd column = diff between [StartTime] and [Start of bar]
3/7/2017 9:40:18 PM	9:38:00 PM	02:18
3/9/2017 6:50:07 PM	6:50:00 PM	00:07
3/9/2017 5:30:23 PM	5:30:00 PM	00:23
3/9/2017 9:30:26 PM	9:30:00 PM	00:26
3/9/2017 4:51:25 PM	4:50:00 PM	01:25
3/9/2017 9:00:05 PM	8:58:00 PM	02:05
3/9/2017 5:00:06 PM	4:58:00 PM	02:06
3/9/2017 9:10:19 PM	9:06:00 PM	04:19
3/9/2017 7:02:24 PM	6:58:00 PM	04:24
3/10/2017 5:30:19 PM	5:30:00 PM	00:19
3/10/2017 6:50:44 PM	6:50:00 PM	00:44
3/10/2017 9:40:17 PM	9:38:00 PM	02:17
3/10/2017 7:32:33 PM	7:30:00 PM	02:33

[wardzynski]

Maybe I should try using the dual() function and sort it by expression?

[wardzynski]

I solved it using the frac() function.

=time(frac(StartTime),'mm:ss')-time(frac([Start of Bar]),'mm:ss')
Thank you for your help Sunny!

[wardzynski]

Sunny,

One last question, how do I change the formula dynamically if the timeframe is changed to 9 min, 13 min or 16 min ?

[MarcoWedel]

Hi,

maybe also possible:

Time(Floor(Frac(StartTime),'00:08','15:30')) as [WHAT I WANT]

hope this helps

regards

Marco