Checking to see if a form was completed within a date range, and by who

user_13758519 0
Viewed 43

I'm trying to automate our truck check sheet. I want to check if a technician completed their truck check within a time frame, and if so then what was the truck number of the truck they were driving.

= if(B$2>today(),"",(if(COUNTIFS(Data!$A:$A,"*"&$A13&"*",Data!$B:$B,"<="&B$2,Data!$B:$B,">="&(B$2-7))=0,"Missed","Complete"))&" "&if(COUNTIFS(Data!$A:$A,"*"&$A13&"*",Data!$B:$B,"<="&B$2,Data!$B:$B,">="&(B$2-7))=0,"","Truck"&" "REGEXREPLACE(if(COUNTIFS(Data!$A:$A,"*"&$A13&"*",Data!$B:$B<="&B$2,Data!$B:$B,">="&(B$2-7))=0,"",Data!C:C),"\D+", "")))

This formula works for checking if its complete or not, and works to input the word truck if its complete, but its failing in the regexreplace section. The if(countif =0,"",C:C) isnt giving back the correct row, im not sure how to get the C row that lines up with the countif.

Here is a link to my sheet - https://docs.google.com/spreadsheets/d/1rcPEpTu-F7qhoHixv8_FIuxkCuaMamqrKPlTlNu6kWQ/edit?usp=sharing

Also if im over complicating this please let me know! Any help is greatly appreciated.

google-sheets-formula google-sheets
1 Answers

Try this one:

=IF(
  B$2 > TODAY(),
    "",
    IFNA(
        "Complete Truck(s) "
      & TEXTJOIN(
          ", ",
          True,
          FILTER(
            REGEXREPLACE(Data!$C:$C, "\D+", ""),
            IFERROR(SEARCH($A3, Data!$A:$A), 0) > 0,
            Data!$B:$B <= B$2,
            Data!$B:$B >= B$2-7
          )
        ),
      "Missed"
    )
)

enter image description here

user_2798060
Related