I’m an English teacher so anything math based can cause me a little trouble and doubt, especially when it comes to grading late assignments. I usually have two or three calendars hung up around my desk and each time I need to calculate late point deductions, I point at each little square at the calendar. (1, 2, 3, 4, …). I have to exclude weekends and school holidays, so trying to determine how many late points are actually deducted has been a nightmare!
FINALLY, I have created a solution! No more triple calendars for me! Now all I have to do is type in a few dates and voila! I have the number of days minus weekends and minus school holidays.
It’s very easy to use and modify for your own classroom. The Date Due and Date Turned In are quite obvious. The # of Days is what the function determines are the number of days between the two given dates. This was accomplished through the Google Sheets function NETWORKDAYS. This function automatically takes Saturdays and Sundays into account as weekends and will not include them in the totals. The holidays are in a separate column formatted as dates we don’t have school.
The video linked here explains that when you change the holiday dates, you’ll have to make sure the functions for each of the cells in column C (# of Days) has the right set of cells as the holiday dates.
The Points Off column is a simple Product function with a subtraction. It is =product(C#, 5)-5)** where # is the number cell of the C column. The English department at our school has a consistent policy of 5 points off per day late. All I need to do is have the cell multiply 5 by the number of days determined by the NETWORKDAYS function.
Note: If you look at row 4, you’ll see the Date Due and the Date Turned In are the same. The NETWORKDAYS function does not nullify the date itself. To prevent my calculations from deducting unnecessary points from a student’s grade, I just added a subtraction function, which is the -5)** you see above. I can also easily calculate the final grade by putting in what the student earned before taking away late points. This was done with a simple subtraction function.
If you’d like your own copy, click here. The link will automatically ask you to make your own copy so you can modify the sheet however you need.