2

Question:

Given a list of pay periods and sales dates. For each sales date, determine the next pay day.

Pay periods (given) Sale date (given) Pay day (To be generated)
June 4 June 4 June 4
June 17 June 4 June 4
June 30 June 4 June 4
July 15 June 5 June 17
June 5 June 17
June 12 June 17
June 16 June 17
June 18 June 30
June 22 June 30
June 24 June 30
June 28 June 30
June 30 June 30
July 1 July 15
July 7 July 15
July 8 July 15
July 9 July 15
July 10 July 15

Google Sheets: Yellow = given data, Blue = answer to be generated

Clostest I've gotten:

=arrayformula(filter($A$2:$A,abs($A$2:$A-B2)=min(abs($A$2:$A-B2))))

This formula is incorrect as for some of the sales date, the pay day is earlier. The sale date must always come before the pay day because in real life, you're only paid after you make a sale, not before.

player0
  • 124,011
  • 12
  • 67
  • 124
Don
  • 45
  • 6

3 Answers3

2

try:

=BYROW(B2:B, LAMBDA(x, SINGLE(FILTER(A2:A, A2:A>=x))))

enter image description here

or from another tab:

=BYROW(B2:B, LAMBDA(x, SINGLE(FILTER('New tab'!A2:A, 'New tab'!A2:A>=x))))
player0
  • 124,011
  • 12
  • 67
  • 124
  • 1
    Very slick solution. I'd throw a SORT() around the FILTER() to avoid issues if the pay dates aren't ordered. – John K. Oct 05 '22 at 15:05
  • Very efficient! Is there a way to get the formula to stop any output if Sale date is blank? For example, in the google sheets you can see that John's method stops if sale date is blank but this method keeps outputting "July 4" when sale date is blank. – Don Oct 13 '22 at 18:23
  • I added array formula and check if Sales date is blank prior to your formula. This seems to work but is there something to make this more efficient? "=ARRAYFORMULA(if(B2:B="",,BYROW(B2:B, LAMBDA(x, SINGLE(FILTER('New tab'!A2:A, 'New tab'!A2:A>=x))))))" – Don Oct 13 '22 at 18:40
  • @Don nope. this is it: `=ARRAYFORMULA(if(B2:B="",,BYROW(B2:B, LAMBDA(x, SINGLE(FILTER('New tab'!A2:A, 'New tab'!A2:A>=x))))))` – player0 Oct 13 '22 at 20:57
1

I'm a fan of SQL, so here's an intuitive solution using QUERY():

=BYROW(B2:B, LAMBDA(x, 
    QUERY(
        A:A, "
        SELECT 
            A 
        WHERE 
            A >= DATE '"&TEXT(x, "yyyy-mm-dd")&"' 
        ORDER BY 
            A 
        LIMIT 
            1 
        LABEL 
            A ''
        "
        , 1)
    ))

I threw it in your Google Sheet so you can see it in action!

EDIT: I wrapped this in a BYROW() so you could use it in one cell, if desired.

John K.
  • 480
  • 4
  • 8
  • What if the Pay periods (given) was not on the same sheet? What if was on a different sheet called 'New tab'? In the SQL, could you replace A with 'New tab'!A – Don Oct 05 '22 at 02:29
  • Just change the query range `A:A` to `'New tab'!A:A`. No need to change anything within the SQL unless the column letter changes. – John K. Oct 05 '22 at 14:53
  • That works well John! Also, is there a way to make it more efficient by typing the formula just the first row... and then for all the rows below, the calculation is auto-filled? Ie. using ArrayFormula, Byrow, or Index? – Don Oct 13 '22 at 18:24
  • @Don Yes. Just edited my answer to reflect your desire on that. – John K. Oct 13 '22 at 18:56
0

If "Pay dates" is in ascending order, you can try the formula below:

G2 = arrayformula(INDEX($A$2:$A$5,MATCH(MIN(ABS($A$2:$A$5-B2)),ABS($A$2:$A$5-B2),0)))

H2 = index($A$2:$A$5,match($G2,$A$2:$A$5)+1)

I2 = if(G2<B2,H2,G2)

Then:

F2 = if(arrayformula(INDEX($A$2:$A$5,MATCH(MIN(ABS($A$2:$A$5-B2)),ABS($A$2:$A$5-B2),0)))<B2,index($A$2:$A$5,match(arrayformula(INDEX($A$2:$A$5,MATCH(MIN(ABS($A$2:$A$5-B2)),ABS($A$2:$A$5-B2),0))),$A$2:$A$5)+1),arrayformula(INDEX($A$2:$A$5,MATCH(MIN(ABS($A$2:$A$5-B2)),ABS($A$2:$A$5-B2),0))))

Google sheets sample

Hope it will be helpful.

freemangifts
  • 144
  • 6
  • Use [code fences](https://stackoverflow.com/help/formatting) to format your code. [Test formatting](https://meta.stackexchange.com/questions/3122/formatting-sandbox). – TheMaster Oct 05 '22 at 07:01