Stock market 15 minute calendar

[erickguz]

Hi,

So, I'm having a rough go at it trying to create a calendar without manually entering every 26th row.

I am attempting to create a 15 minute calendar for Stocks.

For example:
COL A
9/3/19 9:30 AM
9/3/19 9:45 AM = WORKDAY(A1,1,[holidays])+15/1440
9/3/19 10:00 AM = WORKDAY(A1,1,[holidays])+15/1440
9/3/19 10:15 AM = WORKDAY(A1,1,[holidays])+15/1440

I need 2,000 "dates", trading days only.


I am having trouble combining WORKDAY and TIME FUNCTION for the next trading day. The trading session lasts from 9:30 AM until 3:45 PM each day, and then goes to the next trading day.

For example:

9/3/19 3:45 PM
9/4/19 9:30 AM = ?????
9/4/19 9:45 AM

Is it possible to create such a trading calendar?

 

[Phuoc]

9/3/19 9:30 AM in A1, try this the formula in A2 and copy down:

=IF(ROUND(MOD(A1,1),0)*1440= 945,WORKDAY(A1,1,[holidays])+570/1440,A1+15/1440)

 

[erickguz]

Hi - unfortunately, it doesn't stop at 3:45 pm. Keeps going throughout entire day.

[TABLE="width: 92"]
<colgroup><col></colgroup><tbody>[TR]
[TD]9/13/19 14:30[/TD]
[/TR]
[TR]
[TD]9/13/19 14:45[/TD]
[/TR]
[TR]
[TD]9/13/19 15:00[/TD]
[/TR]
[TR]
[TD]9/13/19 15:15[/TD]
[/TR]
[TR]
[TD]9/13/19 15:30[/TD]
[/TR]
[TR]
[TD]9/13/19 15:45[/TD]
[/TR]
[TR]
[TD]9/13/19 16:00[/TD]
[/TR]
[TR]
[TD]9/13/19 16:15[/TD]
[/TR]
[TR]
[TD]9/13/19 16:30[/TD]
[/TR]
[TR]
[TD]9/13/19 16:45[/TD]
[/TR]
[TR]
[TD]9/13/19 17:00[/TD]
[/TR]
[TR]
[TD]9/13/19 17:15[/TD]
[/TR]
[TR]
[TD]9/13/19 17:30[/TD]
[/TR]
[TR]
[TD]9/13/19 17:45[/TD]
[/TR]
[TR]
[TD]9/13/19 18:00[/TD]
[/TR]
</tbody>[/TABLE]

 

[Phuoc]

Sorry, try this:

=IF(ROUND(MOD(A1,1)*1440,0)= 945,WORKDAY(A1,1,[holidays])+570/1440,A1+15/1440)

 

[erickguz]

It worked very well. Thank you!

I also revised for decreasing increments: =IF(ROUND(MOD(A1,1)*1440,0)= 570,WORKDAY(A1,-1,$B$4:$B$20)+945/1440,A1-15/1440)

I thought the formula would be more complicated but it is knowledgeably simple!

Thank you.