Time Sheet Motivation

My job requires that I keep a time sheet (timesheet?) of my daily activities. This time sheet is just an ordinary .xls Excel file, but it allows me to document exactly how much time I spend on various projects during a given day. I submit this document to my supervisor every Friday, then start a new time sheet the following Monday. It’s not complicated, but man it’s tedious! If I’m being honest, I don’t fill out my time every day, though. When Friday rolls around, I generally have to rack my brain to remember how I spent my week! That’s not really acceptable. The obvious solution is to automate the process so that Windows automatically opens my time sheet before I leave work every day, thereby forcing me to record my time!

Using Windows Task Scheduler to automatically open an Excel file every day should be easy enough, so why am I complicating things by using Python? The main reason is that each weekly time sheet is its own Excel file; I can’t simply tell the Task Scheduler to open C:/Timesheets/Timesheet.xls every day. Python allows me to apply some logic to the process. The Python script is essentially used as a launcher of sorts. Additionally, I didn’t want to have to manually copy/paste/rename a new sheet every Monday morning, so I let Python do that for me, too! To that point, I needed a naming convention, so my script would be able to open files with discretion. The obvious convention was simply: <year>.<month>.<Monday's date>.xls. That ends up looking like this:

Python Scripting

This script’s function is dead simple, but there are a few nuances worth pointing out. The pseduo-code goes something like this:

if it's Monday:
  copy the previous week's time sheet and rename it for this week

open this week's time sheet

The first obvious objective is determining whether it is, in fact, Monday. This is a task best suited for the datetime library. Don’t waste your time figuring out the code, though, just look for the solution on stackoverflow! When the if-Monday condition is met, you’ll also need to know the previous Monday’s date, so that its filename can be constructed and passed on to the function that actually accomplishes the copying. I’d recommend using the datetime.timedelta method for this. When it comes to constructing the filenames from datetime objects, follow my lead, or utilize this handy reference to come up with something yourself.

The last two nuances to this code involve file system operations (copying and opening). There are a ton of ways to accomplish these actions, but if you’re on Windows, just use the os and pathlib libraries.

Go ahead and copy the code below into a .py file somewhere on your hard drive (mine is located in E:/Scripts/ and is named TimesheetLaunch.py). This will be important momentarily when scheduling the launcher to run every day at a prescribed time.

# -*- coding: utf-8 -*-
"""
This script was written to automate the opening of weekly Timesheets.

v1.0 | Andrew Cross | March, 2018
"""

import datetime
from pathlib import Path
import os

rootDir = "E:/Timesheets/"

today = datetime.date.today()
monday = today - datetime.timedelta(days=today.weekday()) #datetime.date

mondayLoc = Path(rootDir + monday.strftime("%y.%m.%d.xls"))

if not mondayLoc.is_file():
    lastMonday = monday- datetime.timedelta(days=7) #datetime.date
    lastMondayLoc = Path(rootDir + lastMonday.strftime("%y.%m.%d.xls"))
    
    os.system('copy %s %s' % (lastMondayLoc, mondayLoc))

os.startfile(mondayLoc)

Task Scheduler

If you’ve never used the Task Scheduler, it’s tucked away in your start menu at Start Menu → Windows Administrative Tools. On the right side of the Task Scheduler window is an Actions box. Under that box, click the button to Create Basic Task…. Go ahead and name your task, then give it a description if you’d like. Click Next

On the following windows, set the Trigger so that this task will run on a daily basis. Click Next, then specify when during the day you’d like the the python launcher to….launch.

You’ll want to make sure the task is set to Start a program, then hit Next. Take note here, because rather than running the script directly, what you’ll want to do is actually run Python, then give it the location of your script as an argument. In my case, I’m using a Python environment installed with Anaconda. Thus, the path to my Python executable is at C:/Users/Andrew/Anaconda3. Within this folder, there are actually two python executables – python.exe and pythonw.exe. The difference between the two is that pythonw.exe will run your code without popping up a terminal window, which is perfect for this case.

Obviously tailor the following to your particular needs, but place the path to your pythonw.exe in the Program/script box, and follow that with the path to your script in the Add arguments (optional) box. Be sure to wrap your script path in quotes!

Additional Tips and Tricks

I’d throw in an additional word of caution, here. If you manipulate the properties of the task in any way, make sure to leave the security option Run only when user is logged on selected. If you try to run your launcher whether the user is logged on or not, the python code will run, and it will open your time sheet, but the Excel instance that gets opened will not be in interactive mode. In other words, no Excel window will pop up and you’ll have no way of accessing your time sheet!