Count missing values under specific date conditions in R


I’m helping a friend who works in the education field and one of the programs her job offers for students during the pandemic is one-on-one tutoring on Zoom. They have employees that take attendance in a Google Sheet but inevitably sometimes they forget to mark attendance. To help her reconcile this problem, I’m trying to figure out some R code that will catch when employees forget to mark attendance. I’ve Googled and attempted if_else commands, pivot_longer, for-loops, etc, but I’m still a (functional) beginner at R so a lot of what I’m reading is going over my head. (FYI R is the only programming language I know so the solution has to be something I can implement and troubleshoot in the future if need be). That said, apologies in advance for not including reproducible code (because I can’t even figure out where to start). But for anyone who’s able to help, here is sample data in a publicly viewable Google Sheet.

The problem

I need to find and/or count the NA values that occur after the tutoring start_date but not the NAs that occur in the future. You can usually tell when an employee forgot to take attendance because:

  1. those NAs fall between weeks where the employee did remember to take attendance and

  2. those NAs occur in the past but never before the tutoring start date

To help visualize this, I have highlighted in yellow most (maybe all) of the NAs that meet this criteria.

Since this isn’t my data/my project there are certain things about it I can’t change just because it’s bad practice (e.g. the fact that the day of the week and the time are in the same cell). But any solutions you have that directly help me resolve the problem are greatly appreciated.



Edit for additional context

To help monitor attendance, I’m creating a flexdashboard and the goal is to include a valueBox (or something similar) that populates the number of tutoring sessions that have NAs that aren’t supposed to. If the program administrator knows there are NAs via the dashboard, she can follow up with her employees asap and have them input the correct code (X, 1, 2, etc.). The problem she’s having now is that several days or weeks can pass by and the employee may forget if the session occurred or if someone was late. Thanks!


How do I fix a Cannot move to target thread (0x1b10ec0) when creating a QApplication object in PyQT5?

A Go framework for building JSON web services inspired by Dropwizard