Irrigation Schedule Start and End Times

Please try at
K13
=IF(L13="","",MOD(L13-$J13/1440,1))

L13
=IF($I13=MATCH("z",INDEX($I$4:$X$8,MAX(($E$4:$H$8=K$11)*$D$4:$D$8),)),$B$7,
IF(INDEX($I$4:$X$8,MAX(($E$4:$H$8=K$11)*$D$4:$D$8),$I13)="x",MOD(INDEX(L14:L$29,MATCH(TRUE,L14:L$29<>"",))-(LEFT($B$4,3)+J13)/1440,1),""))

You say the Monday End times have been filled in manually but why are they all 30 minutes after the start time. They haven't been increased by the 45 minutes.

Are the start times entered manually since you say "can formulas be derived in K13:X28 to determine the Start and End Times". There's no apparent rule for working out the start time. Please clarify.

Edit, Ah, I see, the zones run consecutively, the start time for one being the end time for the previous one plus 45 minutes

@Richard: Start times are 45 minutes after previous End time.

Please try at L13

=IFERROR(IF($I13=MATCH("z",INDEX($I$4:$X$8,MAX(($E$4:$H$8=K$11)*$D$4:$D$8),)),$B$7,
IF(INDEX($I$4:$X$8,MAX(($E$4:$H$8=K$11)*$D$4:$D$8),$I13)="x",MOD(INDEX(L14:L$29,MATCH(TRUE,L14:L$29<>"",))-(LEFT($B$4,3)+$J13)/1440,1),"")),"")

Approximate match lookup for the last cell requires high lookup value, "z" > "x"

=MATCH("z",I4:X4) can find last cell at 13

while Match "x" may not return last cell
=MATCH("x",I4:X4)

A start would be to change the Schedules to 1 to 5 ( as in your original file) as opposed to A to E.

I cannot help with your SPILL error as I don't have 365: I don't have any errors in my "old" 2010!

See attached.

The first file had the days of the week in F4:L8 but you now have them as headings so the formula obviously needs changing.

As I have nowhere near the the expertise of Bo-Ry ( and many others!) I'll leave it to the 365 experts to resolve.

It's no trouble: I just know my limitations!

Since I have modified this version would it be okay to start a new thread?

We have a one subject, one thread rule. Please upload your latest workbook here.

In addition please add some manually calculated results and explain how the Xs in the Schedule A-F E4:L9 area affect the calculations when there may be more than one schedule for a day. For the moment just hard code the Xs and put an exit sub as the first line in the Worksheet SelectionChange event to prevent them being indvertently changed during testing.

Thanks, I hadn't realised that was the new workbook.

I edited my last post before I saw your response. Please address the points I raised.

Your choice of course and I'm not quite sure how to take that remark.

It has been difficult for me to figure out a logical system on how to schedule the zones with days of the week involved with water management for each zone.

If I had some basis to start with it would be better but since I did not is why I am changing the worksheet.

The original formulas do not logically make sense with a different arrangement of the schedule.

I tried to make a new thread but it was closed.

So I am at a dead end and do not know how to continue.

Similarly I don't know how to start addressing this until you answer the questions I posed earlier.

We always ask to see manually added results when the request is not clear. Too much time is spent guessing how someone uses a model and what they want only to find it's misinterpreted. We're often quite good but not psychic.

Remember that when you ask a question in a forum those of us who might be able to help know absolutely nothing about your system and work process or the terminology you use. Unless you have uploaded a workbook we know nothing about how it is laid out and little about what you want done with it or how the results should be presented.

Whilst these things are second nature to you since you live with the workbook all the working day we start with zero knowledge so you need to take us by the hand and explain in quite precise detail your aims and goals as well as stuff like how your data might look different in other situations.
Don't assume we are able to "figure it out".

Put yourself in our position and think about whether what you've posted makes sense and is unambiguous to someone who's never seen it before.
You are asking us for help so help us to be able to help you by providing all the information we need, even if that information seems obvious or intuitive to you.

I entered zones for Monday and Saturday showing that they complete on the end time and each zone is spaced apart with the delays.

Thank you

Why do you not have a given (fixed) start time for whatever is the first "active" zone? i.e start time is always for example 08:00 ?

Originally Posted by BillySpivy

I need to make sure the end time is not during sunny conditions.

Thanks

That's not answering John's question. And now you seem to have added a further criteria to the end time. You specify the end time can not exceed 10:00 AM. How are you defining in advance whether 10:00 AM is sunny, and what does 'sunny' actually mean? Is there some lux criteria or are you saying the end time must be before the sun is visible?

You're not making this any easier to understand.

I see your latest file has changed. This could be simplified I suggest if rather than duplicating the zones having them listed in both the horizontal table in columns G:V and vertically in rows 14:29, do away with the G:V table and introduce a third column for every day in rows 14:29 and mark those colums with Xs. That avoids complicating formulae by needing to match an X zone in the top table with the zone in D14:D29.

Last try

I14
=IF(J14="","",MOD(J14-$F14/1440,1))

J14
=IFERROR(IF($D14=MATCH("z",INDEX(Intersect_X,MATCH(I$12,$E$4:$E$10,),)),$B$6,
IF(INDEX(Intersect_X,MATCH(I$12,$E$4:$E$10,),$D14)="x",MOD(INDEX(I15:I$30,MATCH(TRUE,I15:I$30<>"",))-($B$4)/1440,1),"")),"")

Hadn't realised this had been solved, but here's what I ended up with last night.

Originally Posted by BillySpivy

Good try but it isn't considering the end time. I appreciate yours and everyone's help.

I am doing my best considering I may not be so smart like most people on here.

I believe it does consider the end time since this has been given the range name 'End' and the formulae in the End Time columns use the minimum of the end time (10:00) and the Start time plus the Run Time.

However you have a solution so we'll leave it at that.