Search code examples
google-sheetsgoogle-sheets-formula

Google sheets formula for showing remaining time per month for descending list of weeks

I need a very particular formula that would calculate the hours left in a month after some amount for them was logged each week. We start with 24h and subtract the time in each row. Seems very easy but there are three caveats:

  1. the count has to reset each month and the list is long
  2. it's a dynamic data range so it has to work on an arrayformula or maybe byrow
  3. the newest dates are at the top so the subtraction has to run backwards

This might not sound very clear, but I have prepared an exact data sheet to show this problem. There are some columns with additional information about each row - the weeks also break to not spill over multiple months. Does anyone have any idea?

Solution

  • Here you have an option with SCAN. The SEQUENCE(ROWS(A2:A),1,ROWS(A2:A),-1) stored in the v variable allows to access each row backwards and doing the substractions and restart at 24. The SORT inverts the process so the results are displayed accordingly.

    =SORT(SCAN(,SEQUENCE(ROWS(A2:A),1,ROWS(A2:A),-1),LAMBDA(a,v,IF(INDEX(A2:A,v)="","",IF(INDEX(B2:B,v)<>INDEX(B2:B,v+1),24-INDEX(E2:E,v),a-INDEX(E2:E,v))))),
SEQUENCE(ROWS(A2:A)),0)

    enter image description here

    If you want to put the formula in row 1 use VSTACK:

    =VSTACK("Time left",SORT(SCAN(,SEQUENCE(ROWS(A2:A),1,ROWS(A2:A),-1),LAMBDA(a,v,IF(INDEX(A2:A,v)="","",IF(INDEX(B2:B,v)<>INDEX(B2:B,v+1),24-INDEX(E2:E,v),a-INDEX(E2:E,v))))),
    SEQUENCE(ROWS(A2:A)),0))