What dates are the first zero, and the first non-zero after a specified date

[gooniegirl180]

Hi all,

I have a list of weekly dates in cells D11:D167, and switches of either a 1 (on) or 0 (off) in column E against each date.

For example:

12/5/24 0
19/5/24 0
26/5/24 1
2/6/24 1
9/6/24 1
16/6/24 1
23/6/24 1
30/6/24 0
7/7/24 0
14/7/24 0
21/7/24 1
28/7/24 1
4/8/24 0

If I am looking at this data on 2/6/24, I want to know:
(a) the last date before the switch turns off (i.e. 23/6/24)
(b) the date the switch turns back on again (i.e. 21/7/24)
(c) the date after the result in (b) that is the last date before the switch turns off again (i.e. 28/7/24)

What's the easiest way to do this, because I actually have multiple columns of switches against one set of dates, and I'll need these results for each column of switches. My example is based on the assumption that the switch for 2/6/24 is on, but if it's off, I still want to show the next "on" as answer (b), until we're actually in that week.

I'm intermediate, so if you could include a bit of an explanation on how the answer works, it will help me learn.

Thanks,

 

[SunnyAlv]

Like this ?

Formula
(A)

=LET(data,D2:E14,date,D2:D14,onof,E2:E14,a,DROP(data,MATCH(H3,date,0)),XLOOKUP(1,INDEX(a,,2),INDEX(a,,1),,,-2))

(B)

=LET(data,D2:E14,date,D2:D14,onof,E2:E14,a,DROP(data,MATCH(H3,date,0)),XLOOKUP(1,INDEX(a,,2),INDEX(a,,1),,,2))

(C)

=LET(data,D2:E14,date,D2:D14,onof,E2:E14,a,DROP(data,MATCH(H3,date,0)),XLOOKUP(1,INDEX(a,,2),INDEX(a,,1),,,-1))

View attachment 112180

 

[myall_blues]

@SunnyAlv onof not required?

 

[SunnyAlv]

@SunnyAlv onof not required?

ah my bad, i forgot to show image

 

[gooniegirl180]

Thanks! This works for this instance. What if I had another lot of different switches in column F? Which part of the formula will change?

 

[gooniegirl180]

Actually, SunnyAlv, it's not quite working the way I need it to, but part of that may be because I'm not certain if I'm extrapolating your solutions correctly to the other columns. Here is a sample of my actual data - my query date right now is 02/06/24:

So, (A) = Current End Date: if the switch is currently on, what is the date of the last on switch before it turns off: if it's currently off, then blank;
(B) = Next Start Date: if there are no off switches between 02/06/24 and 29/06/25, then blank, otherwise the first date the switch is on after being off;
(C) = Next End Date: if there are no off switches between 02/06/24 and 29/06/25, then blank, otherwise the last date after (B) that the switch is on before turning off again.

The results I want are as follows:
For column E: (A) = Blank, (B) = 24/11/24, (C) = 06/04/25 which is the date of the last "on" (sorry, I cut off the image)
For column F: (A) = 23/06/24, (B) 11/08/24, (C) = 22/06/25 (last "on" date)
For column G: (A) = Blank, (B) = 25/08/24, (C) = 27/10/24
For column H: (A) = Blank, (B) = 08/09/24, (C) = 20/10/24
For column M: (A) = 29/06/25, (B) = Blank, (C) = Blank - switch is on for the entire year.

The only change I made to your formulae when copying across the columns was to increase the numeral in the first "INDEX" of the Xlookup. If there was more I needed to do, let me know.

Thanks for your help

Gooniegirl180

 

[SunnyAlv]

Actually, SunnyAlv, it's not quite working the way I need it to, but part of that may be because I'm not certain if I'm extrapolating your solutions correctly to the other columns. Here is a sample of my actual data - my query date right now is 02/06/24:

View attachment 112216

So, (A) = Current End Date: if the switch is currently on, what is the date of the last on switch before it turns off: if it's currently off, then blank;
(B) = Next Start Date: if there are no off switches between 02/06/24 and 29/06/25, then blank, otherwise the first date the switch is on after being off;
(C) = Next End Date: if there are no off switches between 02/06/24 and 29/06/25, then blank, otherwise the last date after (B) that the switch is on before turning off again.

The results I want are as follows:
For column E: (A) = Blank, (B) = 24/11/24, (C) = 06/04/25 which is the date of the last "on" (sorry, I cut off the image)
For column F: (A) = 23/06/24, (B) 11/08/24, (C) = 22/06/25 (last "on" date)
For column G: (A) = Blank, (B) = 25/08/24, (C) = 27/10/24
For column H: (A) = Blank, (B) = 08/09/24, (C) = 20/10/24
For column M: (A) = 29/06/25, (B) = Blank, (C) = Blank - switch is on for the entire year.

The only change I made to your formulae when copying across the columns was to increase the numeral in the first "INDEX" of the Xlookup. If there was more I needed to do, let me know.

Thanks for your help

Gooniegirl180

like this ?

im sorry i cant using XL2BB,
I have uploaded the file on google drive and u can download file on google drive here Gooniegirl180

 

[SunnyAlv]

like this ?

im sorry i cant using XL2BB,
I have uploaded the file on google drive and u can download file on google drive here Gooniegirl180

View attachment 112275

im sorry i have an update, update formula on (C) Update

 

[gooniegirl180]

Terima Kasih SunnyAlv, this is a lot closer! The solution isn't yet perfect, but I believe I can work with what you've given me (which is WAY above my skill level, so thank you! I have learned a lot)!.

FYI, here is what isn't working:
(A) is giving me a #N/A error if a switch is on for the remainder of the year.
(B) is giving me a #N/A error if a switch is on for the remainder of the year, OR off for the remainder of the year.

I can fix (B) by enclosing your solution inside an IFERROR(.......,""). However, if I do the same to (A), then (B) returns the date after my query date, in this case, 09/06/24, if a switch is either on or off for the remainder of the year (but the dates are correct if it turns on and/or off after the query date).

Thank you again for your help!

Regards,
gooniegirl180