In my world, there is a difference between work submitted on time and that which is submitted late. I tend to assign a lot of work in my classes, and I expect all of it to be submitted on time. Every time. In addition to my philosophical and professional reasons a student should submit work on time, I consider excuses – any excuse, really – to be lame.
I will illustrate this post with a scenario. For the sake of simplicity, let’s assume I give my students a weekly reading assignment from the textbook. In order to make sure the students are reading key sections of the chapter, I require them to respond to specific open-ended comprehension questions. Perhaps I will do something in class that builds on this content from the book, or perhaps I want them to read this section but don’t want to talk about it in class. It doesn’t matter. What matters is this: I want to be able to tell who read these sections before the class meeting. So, I use my old friend Google Forms and post a set of questions that will guide students’ reading. I also want to send them an immediate confirmation that their responses were received (something I address in a previous post). In a typical class, 93% of the students will submit the reading on time, 4% will submit it late (rarely the same students each week), and 3% will not do the assignment at all (pretty much always the same students each week). This presents our problem: I want to be able to tell the 4%ers that I know their assignment was submitted late by coding that into the automatic response. Is that even possible? Well, Google automatically timestamps each entry in a Form, and if you have the input you can convert it to output. Here’s how.
Step One: Set Up CopyDown to Apply Formulas to New Entries
CopyDown is a simple Google Sheets add-on that will apply the formulas in Row 2 to every subsequent entry in your form. You will need to submit an entry to your form and call it Master Row. Then you can apply CopyDown, and it will automatically do the calculations any time a new entry comes in.
Step Two: Convert the Timestamp to a Number String
The default format for the timestamp in Google Sheets is date and time (e.g., 9/30/2015 8:59:00). This is useful information, but it is not in a format that can be used to compare one value with another. The workaround for this problem is to convert the time and date to a duration. The exact duration this value is referring to, I am not exactly sure. The image below shows you how to do this.
If you convert the first entry in your form to duration format, it will automatically apply this format to all subsequent entries.
Step Three: Remove the Colons from the Timestamp
In order to compare one value to another, you need to make sure both values are a string of numbers. You do not want those pesky colons causing any problems, so the easiest thing to do is to move them. The formula is as follows. The screenshot is below:
=SUBSTITUTE(Cell with Timestamp,“:”,“”)
Step Four: Use an IF Statement to Compare the Timestamp to the Due Date
Once you have set up the previous steps, it’s all downhill from here. You will need to determine the value for the due date, then use an IF statement to compare the time the student submitted against that value. You can find the string for the due date by entering it in a cell in the default timestamp format, then choose the Duration format. For example, 12/5/2016 would become 10250250000. The IF statement looks like this:
=IF(Converted Timestamp>“10250250000”, “Yes”, “No”)
In other words, if the converted timestamp is larger than the target timestamp, then label it is as “Yes, it’s late;” otherwise, “No, it is not late.” It will look like this on the spreadsheet:
Step Five: Set Up FormMule to Send Conditional Responses
If you are familiar with the Google Sheets add-on, FormMule, this last step is easy. If you are new to this tool, you will want to spend some time learning how it works. In essence, you can create different templates based on whether the student submission was on time or not. Students who submit on time will get full credit and a confirmation message, and students who submit late will get a point deduction and a different (slightly punitive) confirmation message. The screenshot of setting up different responses in FormMule is below:
You can apply the point deduction using another IF statement:
=if(Late Status=“Yes”, 200, Full Credit)
It looks like this:
So, that’s it. The whole process takes about 5-10 minutes to set up, and once you have done it a few times it almost feels natural. Almost. This will save you a lot of time in the long run, and it sends important information to the students so they know you know they were late.