Formula simplification request

[willsing5130]

Hi everyone

Full disclosure... I started a similar thread on another forum, but for some reason I am unable to post a reply to it. I have tried several times without success. I hope that you all can help as I guess that this forum may share member with the other.

I created a simplified version of the full version to share on this post, but I am having difficulty with using the XL2BB addon.
In the full version, the data in column B grows throughout the year and usually reaches row 50 and beyond. The formula in column C can only encompass the data in B9 - B42 before it reaches the maximum character limit.
From the simplified version, I placed the data from column B, the formula in column C, and a screenshot of the spreadsheet below.
The data in Column B are pairs of start and end dates.
The formula in column C expands each pair of start/end dates into a set of sequential dates and times that are in 60 minute intervals. The interval is determined (in minutes) by cell E4.
I would appreciate help with a replacement formula that achieves the same outcomes as the current formula in column C. So far, I have not been able to develop a simplified version that works as well as the current version.

Data in column B
Consecutive Campaign Runs Collapsed - Raw
2024-02-20 11:55
2024-02-21 01:14
2024-03-21 01:14
2024-03-21 11:30

Formula in column C
=VSTACK(IFERROR(FILTER(SEQUENCE(24*4*(ROUNDUP((CEILING.MATH(B10,$E$4/1440))-(FLOOR.MATH(B9,$E$4/1440)),0)),1,FLOOR.MATH(B9,$E$4/1440),$E$4/1440),((SEQUENCE(24*4*(ROUNDUP((CEILING.MATH(B10,$E$4/1440))-(FLOOR.MATH(B9,$E$4/1440)),0)),1,FLOOR.MATH(B9,$E$4/1440),$E$4/1440))>=(FLOOR.MATH(B9,$E$4/1440)))*((SEQUENCE(24*4*(ROUNDUP((CEILING.MATH(B10,$E$4/1440))-(FLOOR.MATH(B9,$E$4/1440)),0)),1,FLOOR.MATH(B9,$E$4/1440),$E$4/1440))<=(CEILING.MATH(B10,$E$4/1440))),""),"No Data Available"),IFERROR(FILTER(SEQUENCE(24*4*(ROUNDUP((CEILING.MATH(B12,$E$4/1440))-(FLOOR.MATH(B11,$E$4/1440)),0)),1,FLOOR.MATH(B11,$E$4/1440),$E$4/1440),((SEQUENCE(24*4*(ROUNDUP((CEILING.MATH(B12,$E$4/1440))-(FLOOR.MATH(B11,$E$4/1440)),0)),1,FLOOR.MATH(B11,$E$4/1440),$E$4/1440))>=(FLOOR.MATH(B11,$E$4/1440)))*((SEQUENCE(24*4*(ROUNDUP((CEILING.MATH(B12,$E$4/1440))-(FLOOR.MATH(B11,$E$4/1440)),0)),1,FLOOR.MATH(B11,$E$4/1440),$E$4/1440))<=(CEILING.MATH(B12,$E$4/1440))),""),"No Data Available"),IFERROR(FILTER(SEQUENCE(24*4*(ROUNDUP((CEILING.MATH(B14,$E$4/1440))-(FLOOR.MATH(B13,$E$4/1440)),0)),1,FLOOR.MATH(B13,$E$4/1440),$E$4/1440),((SEQUENCE(24*4*(ROUNDUP((CEILING.MATH(B14,$E$4/1440))-(FLOOR.MATH(B13,$E$4/1440)),0)),1,FLOOR.MATH(B13,$E$4/1440),$E$4/1440))>=(FLOOR.MATH(B13,$E$4/1440)))*((SEQUENCE(24*4*(ROUNDUP((CEILING.MATH(B14,$E$4/1440))-(FLOOR.MATH(B13,$E$4/1440)),0)),1,FLOOR.MATH(B13,$E$4/1440),$E$4/1440))<=(CEILING.MATH(B14,$E$4/1440))),""),"No Data Available"),IFERROR(FILTER(SEQUENCE(24*4*(ROUNDUP((CEILING.MATH(B16,$E$4/1440))-(FLOOR.MATH(B15,$E$4/1440)),0)),1,FLOOR.MATH(B15,$E$4/1440),$E$4/1440),((SEQUENCE(24*4*(ROUNDUP((CEILING.MATH(B16,$E$4/1440))-(FLOOR.MATH(B15,$E$4/1440)),0)),1,FLOOR.MATH(B15,$E$4/1440),$E$4/1440))>=(FLOOR.MATH(B15,$E$4/1440)))*((SEQUENCE(24*4*(ROUNDUP((CEILING.MATH(B16,$E$4/1440))-(FLOOR.MATH(B15,$E$4/1440)),0)),1,FLOOR.MATH(B15,$E$4/1440),$E$4/1440))<=(CEILING.MATH(B16,$E$4/1440))),""),"No Data Available"))

 

[StephenCrump]

Welcome to the Forum!

Full disclosure... I started a similar thread on another forum ...

Thanks for this, but please provide the link next time, which in this case is: Formula Simplification

And if you will always be using Excel 365, it would be good if you could update your Account details.

Give this a try:

	A	B	C	D	E
1
2
3
4					120
5
6
7
8
9		20 Feb 2024 11:55	20 Feb 2024 10:00
10		21 Feb 2024 1:14	20 Feb 2024 12:00
11		21 Mar 2024 1:14	20 Feb 2024 14:00
12		21 Mar 2024 11:30	20 Feb 2024 16:00
13		7 Apr 2024 10:05	20 Feb 2024 18:00
14		7 Apr 2024 16:45	20 Feb 2024 20:00
15			20 Feb 2024 22:00
16			21 Feb 2024 0:00
17			21 Feb 2024 2:00
18			21 Mar 2024 0:00
19			21 Mar 2024 2:00
20			21 Mar 2024 4:00
21			21 Mar 2024 6:00
22			21 Mar 2024 8:00
23			21 Mar 2024 10:00
24			21 Mar 2024 12:00
25			7 Apr 2024 10:00
26			7 Apr 2024 12:00
27			7 Apr 2024 14:00
28			7 Apr 2024 16:00
29			7 Apr 2024 18:00
30
31
Sheet1

Cell Formulas
Range	Formula
C9:C29	C9	=LET(d,B9:B14,seq,SEQUENCE(ROWS(d)/2,,,2),s,FLOOR(INDEX(d,seq),E4/1440),N,ROUND(1+(CEILING(INDEX(d,1+seq),E4/1440)-s)*1440/E4,0),m,SEQUENCE(,MAX(N),0),TOCOL(IFS(m<N,s+m*E4/1440),2))
Dynamic array formulas.

I don't know if your time ranges can overlap? If so, you may want to wrap in UNIQUE() to eliminate duplicate times where the overlaps occur.

 

[Peter_SSs]

Full disclosure... I started a similar thread on another forum

Welcome to the MrExcel board & thanks for telling us that you have posted elsewhere as well (cross-post).
As well as telling us about cross-posts, in the future please also provide links to them per #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

but I am having difficulty with using the XL2BB addon.

Could that problem be this? XL2BB Icons greyed out

 

[willsing5130]

Welcome to the Forum!


Thanks for this, but please provide the link next time, which in this case is: Formula Simplification

And if you will always be using Excel 365, it would be good if you could update your Account details.

Thanks! Works like a charm!

 

[Peter_SSs]

Thanks! Works like a charm!

Just checking as I notice there are a couple of differences in the results between your formula and Stephen's. I have not tried to decipher either formula and these differences may or may not matter to you.

• I have extended the range in Stephen's formula to include some empty cells in column B as I noticed that your formula had a provision for "No Data Available". Consequently there is a difference in the red cells at the bottom of the ranges below
  
  
• As you can see there is also an extra result in C17 that is not in the post #1 formula results.

24 11 12.xlsm
	B	C	D	E
4				120
5
6
7
8		Post #2 Formula	Post #1 Formula
9	20 Feb 24 11:55	20 Feb 24 10:00	20 Feb 24 10:00
10	21 Feb 24 1:14	20 Feb 24 12:00	20 Feb 24 12:00
11	21 Mar 24 1:14	20 Feb 24 14:00	20 Feb 24 14:00
12	21 Mar 24 11:30	20 Feb 24 16:00	20 Feb 24 16:00
13	07 Apr 24 10:05	20 Feb 24 18:00	20 Feb 24 18:00
14	07 Apr 24 16:45	20 Feb 24 20:00	20 Feb 24 20:00
15		20 Feb 24 22:00	20 Feb 24 22:00
16		21 Feb 24 0:00	21 Feb 24 0:00
17		21 Feb 24 2:00	21 Mar 24 0:00
18		21 Mar 24 0:00	21 Mar 24 2:00
19		21 Mar 24 2:00	21 Mar 24 4:00
20		21 Mar 24 4:00	21 Mar 24 6:00
21		21 Mar 24 6:00	21 Mar 24 8:00
22		21 Mar 24 8:00	21 Mar 24 10:00
23		21 Mar 24 10:00	21 Mar 24 12:00
24		21 Mar 24 12:00	07 Apr 24 10:00
25		07 Apr 24 10:00	07 Apr 24 12:00
26		07 Apr 24 12:00	07 Apr 24 14:00
27		07 Apr 24 14:00	07 Apr 24 16:00
28		07 Apr 24 16:00	07 Apr 24 18:00
29		07 Apr 24 18:00	No Data Available
30		00 Jan 00 0:00
31
willsing5130

Cell Formulas
Range	Formula
C9:C30	C9	=LET(d,B9:B16,seq,SEQUENCE(ROWS(d)/2,,,2),s,FLOOR(INDEX(d,seq),E4/1440),N,ROUND(1+(CEILING(INDEX(d,1+seq),E4/1440)-s)*1440/E4,0),m,SEQUENCE(,MAX(N),0),TOCOL(IFS(m<N,s+m*E4/1440),2))
D9:D29	D9	=VSTACK(IFERROR(FILTER(SEQUENCE(24*4*(ROUNDUP((CEILING.MATH(B10,$E$4/1440))-(FLOOR.MATH(B9,$E$4/1440)),0)),1,FLOOR.MATH(B9,$E$4/1440),$E$4/1440),((SEQUENCE(24*4*(ROUNDUP((CEILING.MATH(B10,$E$4/1440))-(FLOOR.MATH(B9,$E$4/1440)),0)),1,FLOOR.MATH(B9,$E$4/1440),$E$4/1440))>=(FLOOR.MATH(B9,$E$4/1440)))*((SEQUENCE(24*4*(ROUNDUP((CEILING.MATH(B10,$E$4/1440))-(FLOOR.MATH(B9,$E$4/1440)),0)),1,FLOOR.MATH(B9,$E$4/1440),$E$4/1440))<=(CEILING.MATH(B10,$E$4/1440))),""),"No Data Available"),IFERROR(FILTER(SEQUENCE(24*4*(ROUNDUP((CEILING.MATH(B12,$E$4/1440))-(FLOOR.MATH(B11,$E$4/1440)),0)),1,FLOOR.MATH(B11,$E$4/1440),$E$4/1440),((SEQUENCE(24*4*(ROUNDUP((CEILING.MATH(B12,$E$4/1440))-(FLOOR.MATH(B11,$E$4/1440)),0)),1,FLOOR.MATH(B11,$E$4/1440),$E$4/1440))>=(FLOOR.MATH(B11,$E$4/1440)))*((SEQUENCE(24*4*(ROUNDUP((CEILING.MATH(B12,$E$4/1440))-(FLOOR.MATH(B11,$E$4/1440)),0)),1,FLOOR.MATH(B11,$E$4/1440),$E$4/1440))<=(CEILING.MATH(B12,$E$4/1440))),""),"No Data Available"),IFERROR(FILTER(SEQUENCE(24*4*(ROUNDUP((CEILING.MATH(B14,$E$4/1440))-(FLOOR.MATH(B13,$E$4/1440)),0)),1,FLOOR.MATH(B13,$E$4/1440),$E$4/1440),((SEQUENCE(24*4*(ROUNDUP((CEILING.MATH(B14,$E$4/1440))-(FLOOR.MATH(B13,$E$4/1440)),0)),1,FLOOR.MATH(B13,$E$4/1440),$E$4/1440))>=(FLOOR.MATH(B13,$E$4/1440)))*((SEQUENCE(24*4*(ROUNDUP((CEILING.MATH(B14,$E$4/1440))-(FLOOR.MATH(B13,$E$4/1440)),0)),1,FLOOR.MATH(B13,$E$4/1440),$E$4/1440))<=(CEILING.MATH(B14,$E$4/1440))),""),"No Data Available"),IFERROR(FILTER(SEQUENCE(24*4*(ROUNDUP((CEILING.MATH(B16,$E$4/1440))-(FLOOR.MATH(B15,$E$4/1440)),0)),1,FLOOR.MATH(B15,$E$4/1440),$E$4/1440),((SEQUENCE(24*4*(ROUNDUP((CEILING.MATH(B16,$E$4/1440))-(FLOOR.MATH(B15,$E$4/1440)),0)),1,FLOOR.MATH(B15,$E$4/1440),$E$4/1440))>=(FLOOR.MATH(B15,$E$4/1440)))*((SEQUENCE(24*4*(ROUNDUP((CEILING.MATH(B16,$E$4/1440))-(FLOOR.MATH(B15,$E$4/1440)),0)),1,FLOOR.MATH(B15,$E$4/1440),$E$4/1440))<=(CEILING.MATH(B16,$E$4/1440))),""),"No Data Available"))
Dynamic array formulas.