CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Jon Beckner
Jon Beckner

Reputation: 57

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

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

Upvotes: 0

Views: 517

Answers (1)

kaitlynmm569
kaitlynmm569

Reputation: 1715

Here is a formula that should work:

=iferror(arrayformula(if($A$2:$A<>"", if(iferror(match("Yes", $B$1:$B, 0),"x")=row($A$2:$A), DATEDIF(indirect("A"&row(LOOKUP(2,1/(indirect("A1:A"&match("Yes", $B$1:$B, 0)-1)<>""),$A$1:$A))), TODAY(), "D"), DATEDIF($A$2:$A, TODAY(), "D")), "")))

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

Upvotes: 1

Related Questions