Sequential Times within range?

[Painzstake]

I'm customising a social media bot that autoposts from my notetaking program. What I am trying to figure out is seqential timestamping within a defined range. I've been able to get parts working with IF statements, but I'm struggling to get the 'queue' portion to work. Here's what I'm looking to do;

• If the # column (A) is a decimal, then it's to be scheduled now. Preceeding posts rise in increments defined by F2.
• If the # column (A) is a whole number, it joins the queue (B). The queue is a series of seqential times defined by F1, F3, and F4. Schedule interval is when posts are to be made, start and end define the seqenece's daily extents. When the sequence exceeds the end time or result in an even value, it starts a new day at the beginning of the start time.
• As I'm adapting someone else's sheet, the result of F6 is a scripted text. I used the following to pull the various time values, though I imagine there may be an easier way. TIME(IF(ISNUMBER(SEARCH("Hour",Settings!$F$1)),LEFT(Settings!$F$1,FIND("",Settings!$F$1)-1)),IF(ISNUMBER(SEARCH("Minute",Settings!$F$1)),LEFT(Settings!$F$1,FIND(" ",Settings!$F$1)-1),0),0).
• The formula needs to work as an array.

I hope that's clear, I'd appreciate your help if you're able to solve this!

Example:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]#[/TD]
[TD]Queue (E.g.)[/TD]
[TD][/TD]
[TD]Schedule Interval:[/TD]
[TD]6 hours[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0.1[/TD]
[TD]NOW()[/TD]
[TD][/TD]
[TD]Minimum Spacing:[/TD]
[TD]00:15:00[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]0.2[/TD]
[TD]NOW()+00:15[/TD]
[TD][/TD]
[TD]Start at:[/TD]
[TD]10:00[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]0.3
[/TD]
[TD]NOW()+00:30[/TD]
[TD][/TD]
[TD]End at:[/TD]
[TD]22:00[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]0.4
[/TD]
[TD]NOW()+00:45[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]1[/TD]
[TD]11/03/18 10:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]2[/TD]
[TD]11/03/18 16:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]3[/TD]
[TD]11/03/18 22:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]4[/TD]
[TD]12/03/18 10:00
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]5[/TD]
[TD]12/03/18 16:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]6[/TD]
[TD]12/03/18 22:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]7[/TD]
[TD]13/03/18 10:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Worf]

Yes, it explains a lot. Google sheets uses Java script, not VBA. You need a JS custom function; unfortunately, I cannot write it.
I suggest you post a question on a forum with a JS section, such as Stack Overflow.

 

[Painzstake]

After several hours tinkering with this problem I was able to come up with a solution. In case <g class="gr_ gr_207 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="207" data-gr-id="207">anyody</g> happens to <g class="gr_ gr_208 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="208" data-gr-id="208">stumlbe</g> across this post in the future;

I changed the import location of the old E1 to another cell. The 'new' E1, references that cell using <bdo dir="ltr">| =TIME(IF(ISNUMBER(SEARCH("hour",B3)),LEFT(B3,2),0),IF(ISNUMBER(SEARCH("minute",B3)),LEFT(B3,2),0),0).</bdo>

Column B's formula | =ARRAYFORMULA(IF(ISBLANK(B2:B),"",IF(B2:B<1,CEILING(NOW(),$E$2)+((B2:B*10-1)*$E$2),IF($E$5>=B2:B,TODAY(),TODAY()+(FLOOR(B2:B-1,$E$5)/$E$5))+$E$3+IF(A2:A>$E$5,MOD(A2:A,$E$5+1),A2:A)*$E$3)))

E5 is a new addition that helps further simplify the formula | <bdo dir="ltr">=ROUNDDOWN((B14-B13)/C3,0)

Big thanks to Worf and TBH777 for giving this problem a shot. I appreciate the time you took to get come up with solutions.</bdo>

Example:
[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]#[/TD]
[TD]Queue (E.g.)[/TD]
[TD][/TD]
[TD]Schedule Interval:
[/TD]
[TD]06:00:00[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0.1[/TD]
[TD]NOW()[/TD]
[TD][/TD]
[TD]Minimum Spacing:[/TD]
[TD]00:15:00
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]0.2[/TD]
[TD]NOW()+00:15[/TD]
[TD][/TD]
[TD]Start at:[/TD]
[TD]10:00
[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]0.3[/TD]
[TD]NOW()+00:30[/TD]
[TD][/TD]
[TD]End at:[/TD]
[TD]22:00[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]0.4[/TD]
[TD]NOW()+00:45[/TD]
[TD][/TD]
[TD]Per day:
[/TD]
[TD]3.00
[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]1[/TD]
[TD]11/03/18 10:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]2[/TD]
[TD]11/03/18 16:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]3[/TD]
[TD]11/03/18 22:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]4[/TD]
[TD]12/03/18 10:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]5[/TD]
[TD]12/03/18 16:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]6[/TD]
[TD]12/03/18 22:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]7[/TD]
[TD]13/03/18 10:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]