Spill Array Formula to Values between Cells on Separate Rows

[ScottTemple]

Hello,

I have a spill array that encompasses columns A - AD with the following formula: =CHOOSECOLS(FILTER(Table9,Table9[Date]<>""),1,2,3...

I would like to calculate the difference between two times that are located on separate rows from each other (see below). The formula I would use in a non spill array is =IF(N3>=O2,N3-O2,N3+1-2)*1440, but that will not work in this instance. Any assistance would be greatly appreciated.

 

[ScottTemple]

I have two tabs in one workbook. The first tab contains my data entry sheet (Table9), the second tab contains my spill array with the below formula (columns A - AD). Outside of that spill array, I have various formulas to calculate data (columns AE - BD) that pull from that spill array. In column BE, I would like to create a formula that calculates the difference between a Finish Time (cell O2) and the Start Time (Cell N3), O & N are contained within the spill array. Unfortunately, my company restricts what we plug-ins we can add to our computers, so I'm unable to provide the data sheet using XL2BB.

=CHOOSECOLS(FILTER(Table9,Table9[Date]<>""),1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30)

 

[JamesCanale]

This should do the trick.

MrExcelPlayground20.xlsx
	A													N	O															AD																											BE
1	Date													Start	Finish															AD																											Gap
2	2/21/2024													9:30:00 PM	1:00:00 AM															A
3	2/22/2024													1:15:00 AM	4:10:00 AM															B																											15
4	2/22/2024													4:45:00 AM	6:00:00 AM															D																											35
Sheet30

Cell Formulas
Range	Formula
A2:AD4	A2	=CHOOSECOLS(FILTER(Table9,Table9[Date]<>""),SEQUENCE(30))
BE2:BE4	BE2	=LET(a,CHOOSECOLS(A2#,14),b,CHOOSECOLS(A2#,15),c,DROP(VSTACK("a",b),-1),d,IF(c="a","",a-c),IFERROR(d*1440,""))
Dynamic array formulas.

 

[ScottTemple]

Oh you mad lad, that did the trick! However, my initial formula was slighlty incorrect and I'm not certain how to read your formula. The time formula should read:

=IF(N3>=O2,N3-O2,N3+1-O2)*1440

Instead of

=IF(N3>=O2,N3-O2,N3+1-2)*1440

 

[JamesCanale]

Ah, the day rollover... Try this:

=LET(a,CHOOSECOLS(A2#,14),b,CHOOSECOLS(A2#,15),c,DROP(VSTACK("a",b),-1),d,IF(c="a","",IF(a-c>0,a-c,1+a-c)),IFERROR(d*1440,""))

 

[ScottTemple]

Perfect, thank you! How do you learn stuff like this? I'm good with some formulas, but I don't even know where to find training on this level of Excel.

 

[JamesCanale]

This is the training. I solve problems I find here. I was pretty skilled with excel on Jan 12, 2021. But when I joined this site the next day, it started really taking off. I more than doubled my usefulness. Plus 365 is a wonderful imporovement.

 

[ScottTemple]

Ah gotcha. Another quick question for you, if the data I'm pulling from tab1 is blank, specifically time/date cells, how can we have the spill array return a blank value? Currently, if I'm missing a date or time, it returns a 12:00am or 1/0/1900 value, which will mess with my pivot data later on.

 

[JamesCanale]

You current filter looks for "Date" being blank. You can also filter for time being blank.

So when you filter:
FILTER(Table9,Table9[Date]<>"")

you can add more to it, like
FILTER(Table9,(Table9[Date]<>"")*(Table9[Start Time]<>"")*(Table9[End Time]<>""))

the will filter out more unwanted data.

 

[ScottTemple]

Very interesting, thank you.

 

[ScottTemple]

I updated the formula, but I'm getting an error. Would you mind taking a look? I changed Table9 to Shaver_Data

=CHOOSECOLS(FILTER(Shaver_Data,(Shaver_Data[Date]<>"")*(Shaver_Data,(Shaver_Data[Start Time]<>"")*(Shaver_Data,(Shaver_Data[Finish Time]<>"")*(Shaver_Data,(Shaver_Data[Insp Start Date]<>"")
*(Shaver_Data,(Shaver_Data[Insp Complete Date]<>"")),SEQUENCE(30))