I need to calculate total days from past date to today but also check if checkbox is false-CodePudding

I have a spreadsheet that gathers form submissions. I need to calculate the days since submission which I can do. This function still calculates even if cell is empty which is no good. =DATEDIF(A3, TODAY(), "D") I tried this =IF(B3 = "FALSE",DATEDIF(A3, TODAY(), "D")) but it did not work

Where I need help is to not count the empty cells, and stop the count when a checkbox is checked.

Use case: Form submission date is 6/12 and todays date is 6/23 so the count would be 11. tomorrow the count would be 12 and so on. If checkbox is checked the count would not increase.

Example sheet: https://docs.google.com/spreadsheets/d/1OKFoS17le-Y5SAOecoLE4EJxiKqKVjRLRHtMzwHNwxM/edit?usp=sharing

CodePudding user response：

Edit

All issues solved, working perfectly :)

Here is a formula that should work:

=arrayformula(if(A2:A<>"", if(iferror(match(true, B1:B, 0),"x")=row(A2:A), DATEDIF(indirect("A"&row(LOOKUP(2,1/(A1:A5<>""),A1:A5))), TODAY(), "D"), DATEDIF(A2:A, TODAY(), "D")), ""))

The thing is that if the first item in the column is checked, it does pull the text from the header.

I hope this helps! Please let me know if you have any questions.