Numbers: Auto-adjust sequential stop times with run/soak

This is easy to do - as long as your data is setup correctly.

The short answer is that the formula for cell H12 should be simply:

=G2+E2+F2

in other words, the start time, plus the run time, plus the soak time.

From here, it's easy to link the next start time to the previous zone's end time.

This will work automatically, AS LONG as the values in columns E and F (Run Time and Soak Time) are set as Duration fields.

If they are, and Numbers knows they represent minutes, it will happily add them to a date/time value.

In my recreation of your table, I have set columns E and F as Duration (Inspector -> Format -> Cell -> Data Format)

and you can see the formula in H2 references these cells with a simple addition.

OK, now it makes a lot more sense. It wasn't completely obvious before, but now I see that each zone has two cycles. For any given run you need to determine the end time of either the previous zone, or the previous cycle in this zone, depending on whether it's first or second.

An interesting challenge :)

Here's what I came up with.

I made a couple of changes to your table, just to make some of the calculations easier.

First, I added a new 'Zone #' column which contains just the zone number (this makes it easier to find the previous zone, without having to parse out the "Zone #1-1 Front" label each time).

I also crafted a fairly complex formula to perform filters on the table to find the previous zone/cycle/run on which to base each zone's start time. While more complex than simply relying on the previous row's data (which was my original assumption), it also makes the sheet more robust since it dynamically 'finds' the previous zone's runtime, so the zones can be in any order and the formula should still work.

Here's the table I came up with:

You can see my new column C, which uses the formula:

=LEFT(TEXTAFTER(B,"#",1),1)

in cell C2, to automatically extract the zone number from column B. It might be possible to eschew this and use the values in column A, but that's up to you.

The simple formula in the Stop column, cell I2 is:

=H2+F2+G2

which calculates each row's Stop time based on its Start time + Run time + Soak time.

The real magic happens in cell H2 with the formula:

=LET(this_zone,C2,

    this_cycle,E2,

    this_run,J2,

    this_days,D2,

    IF(this_cycle=1,

        FILTER(H,(D=this_days)×(J=this_run−1),TIME(4,0,0))+FILTER(F,(D=this_days)×(J=this_run−1),0),

        FILTER(I,(C=this_zone)×(E=this_cycle−1),0))

    )

It uses LET() (available in Numbers 14.4 and later) to grab certain values and store them in named variables. This just makes it easier to read and follow the formula. The heavy lifting happens in the IF() statement:

IF(this_cycle=1...

Checks if this is the first cycle for this zone and chooses one of two formulas depending on the result.

If this is the first cycle for this zone, we run:

        FILTER(H,(D=this_days)×(J=this_run−1),TIME(4,0,0))+FILTER(F,(D=this_days)×(J=this_run−1),0),


This runs a FILTER() against column H (Start Time) to find the matching entry where the 'Days' value matches this row's 'Days', and where the run number is the previous run. To this, it adds that row's Run time, using another FILTER().

If there is no previous run (it's the first run of the day, it defaults to 4 AM (via TIME(4,0,0))

If this isn't the first cycle, it uses a different FILTER() to find the stop time of the previous run in this cycle:

        FILTER(I,(C=this_zone)×(E=this_cycle−1),0))


This simply finds the Stop time for the row that matches this row's Zone, and where the Cycle number is 1 less than this row's cycle (so, theoretically, the formula will support any number of cycles per row).

Fill this formula down the column and you should be set.

How does that look for you?

> That looks interesting but in order for me to digest it and see just where the code is would it be possible for you to share your table with me?

Here you go:

https://www.icloud.com/numbers/0e0cqs9FfG0CBXUJEJCBj4o6w#Sprinklers

> An immediate question, you have I1 labeled as Stop, my initial thought is should that be "Complete" or another word to indicate that is the total cycle time? I look at the Stop Time being Start Time + Run Time. I can see that you are using Run Time + Soak Time.

The column headers are just a label. You can use whatever you want. The formulas I wrote use either Start Time and Run time (if this is the first cycle for this zone), or Stop (aka Complete) Time if this is a subsequent cycle.

> Another question, what if I would find it necessary to add an additional Cycle to any of the Zones?

I already addressed that:

> This simply finds the Stop time for the row that matches this row's Zone, and where the Cycle number is 1 less than this row's cycle (so, theoretically, the formula will support any number of cycles per row).

> If you can follow the run number in Col "I" you can see that the next zone to run has either started right after one has stopped or is waiting for the soak period is done.

That's a whole different level of complexity that was not apparent from your original post.

What is sounds like you want is the Start time of any given zone to equal the Start time of the previous 'Run' + that zone's Run Time.

I'm having trouble, though, working out the exact flow you're aiming for (no pun intended).

Can you elaborate the actual logic you want to apply... I think it's something like

For any given 'zone' check it's 'Run' order - e.g. 'Zone B = 4'

Find the zone that has the preceding Run value for the same Days - e.g. which Zone has the same 'Mon & Thu" schedule with the Run value of 3 => Zone E

Take that schedule (Zone E)'s Start time and add it's Run time and Soak time to determine this zone's Start time.

Is that about right? I want to make sure I understand your flow before I go too far down the wrong path :)

> One last thing, I thought that I asked this before, "Another question, what if I would find it necessary to add an additional Cycle to any of the Zones?"

Did you answer that and I am missing the answer?

I did already answer that when I explained that "... the formula looks up the Stop time for the row that matches this zone and where the Cycle number is 1 less than this row's cycle".

So, for cycle 2, it finds the row with the same zone number AND Cycle = 1

If you had cycle, say, 200, it would look for a row with the same zone number and Cycle = 199

The formula is slightly more complex than it might need to be if there were only two cycles, however, I specifically wrote it so that it should be able to handle any number of cycles for any zone (they don't have to have the same number of cycles, just consecutive cycle numbers).