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:

	A	B	C	D	E
1	#	Queue (E.g.)		Schedule Interval:	6 hours
2	0.1	NOW()		Minimum Spacing:	00:15:00
3	0.2	NOW()+00:15		Start at:	10:00
4	0.3	NOW()+00:30		End at:	22:00
5	0.4	NOW()+00:45
6	1	11/03/18 10:00
7	2	11/03/18 16:00
8	3	11/03/18 22:00
9	4	12/03/18 10:00
10	5	12/03/18 16:00
11	6	12/03/18 22:00
12	7	13/03/18 10:00

<tbody>
</tbody>

 

[tdh777]

Hi.

There's no column F in your example data, but your post mentions formulas / values in F1-F6.

Please provide all the relevant data and formulas to your question.

Thanks.

 

[Painzstake]

Thanks for taking a look tdh777, you're correct ther was a mistake in what I'd written. The F column values are in E. I deleted a column which contained some supurfluous information. I took it out for the sake of clarity, and then forgot to update the text.

 

[tdh777]

Getting closer, slowwwwwwly.

You say that u want the formula in your post , which is in Cell E6 and below in your sample grid to calculate the "queue times" based on the parameters in Cells E1-E4 ?

Looking at your sample data and formula it seems more likely that you want those results in B6 and below, not E6 ?

Also, there is nowhere in your posts showing the formulas where you use E2, E3 or E4 ... so there are still more formulas involved or else you have only posted part of the long array-formula you are working with.

If you want to describe the problem fully and give all the relevant information I can gladly help, but until then it's a game of "guess again !"

Until next post...
Warren.

 

[Painzstake]

Warren, I've adjusted my initial post below, I have reviewed it and all of the information is correct now. I've change the wording to improve clarity as well. If you need more information, happy to provide it.

You're right, there are no formulas in the E column. These are input values, ones that are required for the formula. The formula I pasted was part of my solution.

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 E2.
• If the # column (A) is a whole number, it joins the queue (B). The queue is a series of seqential times defined by E1, E3, and E4. 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 the result in an even value, it starts a new day at the beginning of the start time.
• A side note: as I'm adapting someone else's sheet, the result of E1 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!$E$1)),LEFT(Settings!$E$1,FIND("",Settings!$E
• $1)-1)),IF(ISNUMBER(SEARCH("Minute",Settings!$E$1)),LEFT(Settings!$E$1,FIND(" ",Settings!$E$1)-1),0),0).
• The formula needs to work as an array, originating in cell B2.

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

Example:

	A	B	C	D	E
1	#	Queue (E.g.)		Schedule Interval:	6 hours
2	0.1	NOW()		Minimum Spacing:	00:15:00
3	0.2	NOW()+00:15		Start at:	10:00
4	0.3	NOW()+00:30		End at:	22:00
5	0.4	NOW()+00:45
6	1	11/03/18 10:00
7	2	11/03/18 16:00
8	3	11/03/18 22:00
9	4	12/03/18 10:00
10	5	12/03/18 16:00
11	6	12/03/18 22:00
12	7	13/03/18 10:00

<tbody>
</tbody>

 

[Worf]

You could use a UDF:

Function Queue() As Date
Dim s, interv As Date, b As Range, a, c%
a = Range(Application.Caller.Address).Offset(, -1)                  ' column to the left
s = Split([e1], " ")
Select Case s(1)
    Case "hours"
        interv = TimeSerial(s(0), 0, 0)
    Case "minutes"
        interv = TimeSerial(0, s(0), 0)
End Select
Set b = Range(Application.Caller.Address).Offset(-1, -1)
Select Case Int(a) = a                                              ' is integer
    Case True
        If Int(b) = b Then                                          ' is integer
            Queue = b.Offset(, 1) + interv
            If Queue > Int(b.Offset(, 1)) + CDate([e4]) Then _
            Queue = Int(b.Offset(, 1)) + 1 + CDate([e3])
        Else
            Queue = Int(Now) + CDate([e3])
            c = 1
            Do While Queue < Now And c < 100
              Queue = Queue + interv
              c = c + 1                                             ' safety
            Loop
            If Queue > Int(Now) + CDate([e4]) Then _
            Queue = Int(Now) + 1 + CDate([e3])
        End If
    Case False
        If Len(b) > 0 And IsNumeric(b) Then
            Queue = [e2] + b.Offset(, 1)
        Else
            Queue = Now
        End If
End Select
End Function

 

[Painzstake]

Worf, thanks for taking the time to come up with a solution. I tried to implement it and it's returning the error "Missing ; before statement. (line 1, file "Copy of Timestamp")". How would I fix this, or is there an alternate solution out there? Thanks mate.

 

[Painzstake]

Bump.

 

[Worf]

• This looks like a Java script error, are you using Google Sheets? My code should work on a conventional spreadsheet.
• Although it does not seem to be the case, queue is not a good function name; it may be a keyword somewhere. Change it to “PainQueue” or any other unique name.

 

[Painzstake]

Worf, I am using Google sheets. Does that explain it? I also trialed replacing all entries of "Queue" with "QueueEntry", but it still returns the previous error.