Building a time card system to track hours spent on projects in Notion
When working on projects or to-do items, you might be curious to know the total amount of time you’ve spent on them. (Or, if you’re like me, you enjoy procrastinating by endlessly tweaking your to-do list instead of completing the projects themselves.)
Earlier this year, I started an online course about data analysis with SQL and — when I saw the estimated completion time was 40 hours — I started wondering how long it would take me to finish the course. (Let me rephrase: I knew it would be more than 40 hours. I was curious about how much longer it would take me.)
To answer that question, I added a new Time card database to my existing project and task management system. This lets me "clock in" and "clock out" when working on a task and thus measure the amount of time spent on each one.
Over the past months, I’ve used the time card system whenever working on the “Associate Data Analyst in SQL” course mentioned above. As a result, I can see the amount of time spent on each chapter as well as the total time spent on the course — without needing to connect any plugins to Notion or use a separate app.
If this seems useful to you, the below article includes links to a copy of my time card database (along with the connected tasks and projects pages), a short tutorial showing how to use the system, and notes about how everything works if you are curious to build a similar system in Notion.
Skip to the end: Check out the time card system
If you want to skip reading the words below, you can see everything on Notion by clicking this link. Also, here is a link to the time card database example itself (not super exciting alone). And here is an example of a task with several time cards already populated (which is slightly more exciting than the time card database itself…?)
How the time card system works
If you’d like to understand how I made the above template, check out the notes below.
Part 1: Overview of database relationships and properties
There are three databases in this template: Projects, Tasks, and Time cards. This diagram shows the relationships and main properties.
Database 1: Projects
High-level goals and projects are stored in this database. For example, this is where I keep the page related to the “Associate Data Analyst in SQL” course mentioned earlier.
The properties include the usual stuff like Status, Priority, Created date in addition to a relation property for connecting pages stored in the Tasks database to each page in Projects (and a handy button for adding new tasks).
Database 2: Tasks
This is where I store the individual to-do items that constitute a project. For example, the “Associate Data Analyst in SQL” track includes 13 chapters (e.g. “Introduction to SQL,” “Intermediate SQL,” etc.), so I made a task page for each one. Breaking projects into smaller chunks like this allows me to enjoy that sweet dopamine hit whenever changing the status from “In progress” to “Done” 🤤
Again the properties include the typical items — like Status, Priority, and the relation to the Project database mentioned above. You might also notice the Create punch button and Total hours rollup. I'll explain those later.
Database 3: Time cards
This is the new table where time cards are stored. The main properties include Clock-in and Clock-out dates and times, a relation to the Tasks database, a simple Status drop-down (so it's easier to notice when I forget to punch out), and others. A few more details about these properties are included in the sections below.
Part 2: Setting up the time cards database
I won’t explain every step to replicate the Time cards database and how it's connected to my Tasks page, but here are a few of the key properties and settings. (If something isn't clear, feel free to ask me ✊)
Property: Clock-in and clock-out times
For a time card database, these are probably the two most important columns. I decided to split these into two properties (as opposed to using one property containing a start and end time) for the following reasons: 1) it’s easier on my eyeballs; 2) it’s easier to interact with separate properties when creating buttons and formulas; and 3) Notion’s date properties that include start and end times make me confused.
Property: Minutes
In order to see the duration (in minutes) of each time card, I added a property with the below formula. Why minutes? I thought it would be easier to calculate other properties based on a simple minutes integer instead of something else.
toNumber(if(empty(Clock-out), dateBetween(now(), Clock-in,"Minutes"),
dateBetween(Clock-out, Clock-in, "Minutes")))The formula returns a number according to the below conditions:
- If values exist in both the
Clock-inandClock-outproperties, it returns a number of minutes between the two times using thedateBetweenfunction. - If the
Clock-inproperty includes a time value but theClock-outproperty is empty, it returns a number of minutes between the present time (using thenow()function) and the clock-in time. This is handy when I want to check how long it's been since I clocked in without having to clock out (hope that sentence made sense...)
Property: Hours
In addition to showing the total minutes, I wanted to see the duration of each time card expressed in hours with two decimal places, since something like 2.48 hours is more human-readable than 148 minutes.
To do so, I added another property formula that divides the Minutes property by 60 and rounds the number to two decimal points, as below.
round(((Minutes)/60)*100)/100
Question: Does anyone else think the syntax of the round function in Notion is weird? Why can't we have an easy peasy syntax like round(number, 2) to return a number with two decimal places?
Property: Buttons for punching in and out
For convenience, I created a couple button properties to update the Clock-in, Clock-out, and Status properties accordingly. As you'll see later in this article, I rarely use the Punch in button, but the Punch out button is important since it can be displayed in other databases (e.g. when I'm viewing a time card from a page in the Tasks database).
Part 3: Connecting the Task database
This is the important part, I guess. Here are a few of the key properties for managing time cards and seeing the total time spent on a specific task.
Property: Button to add time cards when viewing a task
I wanted an easy way to create a new time card when viewing a specific task (since that makes more sense than navigating to the Time card database and updating the relation each time I clock in and out).
To accomplish this, I added a button to the Tasks database that would create a new page in the Time cards database and then update a few of the properties on the new page, including the Tasks relation (so the new time card would be related to the currently open task), the start time (set to now), and status (set to Open).
Property: Clocking out from a Task page
To simplify the process of clocking out, I changed the Time cards relation property so it appears as a page section when viewing a page in the Tasks database. This allows you to display the Punch out button created earlier on each task page. Thus, I can close a time card without having to navigate away from the task page I'm viewing.
Property: Seeing the amount of time spent on each task
Alright, alright, alright. This is actually the important part. The goal of this entire endeavor was to see the total hours spent on a specific task (and later project).
The Total hours rollup checks the Time cards relation and returns a sum based on the Hours property in each time card page related to the task. (Again, I'm sorry for these horribly confusing sentences; I hope the screenshot is clearer.)
In the below example, the rollup checks all the time cards related to the task “Functions of Manipulating Data in PosgreSQL” and returns the sum of 7.91 hours (which is almost twice the estimated time to complete the course according to their website 😅).
Part 4: Seeing total hours of a project
At this point, I wanted to display the total number of hours spent on an entire project (not just the individual tasks). At first, I thought I could just create a rollup property in the Projects database to fetch the sum of hours from the related Tasks pages; however, it appears Notion doesn't support rolling up a rollup.
Instead, the best solution I found was to simply display the sum of the Total hours property at the bottom of the table view in the Tasks database — and then add a filter to display a specific project only. For example, as you can see below, I've spent 66.38 hours on tasks related to the "Associate Data Analyst in SQL" project. (Let's not worry about the estimated completion hours on their website 🥲)
That concludes the section about building the time card system and integrating it into my project and task management system.
How to use the time card template
Step 1: Go to the Tasks database in the Notion template.
Step 2: In this example, I’ll open the task called “Assessment: Data Management in SQL.”
Step 3: After opening the task page, click the Create punch button.
Step 4: After clicking the button, a new page is added to the related Time cards database, and the clock-in time of the new page is set to now.
Step 5: When finished working on this task, click the Punch out button.
After that, the status of the time card is changed to “Done” and the clock-out time is updated.
Step 7: You can double check the new time card by navigating to the Time cards database...
…And you can see the newly created time card related to the “Assessment: Data Management in SQL” page.
Step 8: Important part! If you go back to the Tasks database, there is a Total hours rollup property that displays the sum of all related time cards. It looks like I've spent over 60 hours on this project 🥲
The end: Let me know your thoughts!
That concludes the dive into my time card database in Notion. If you have any questions or suggestions — or just want to chat about project management — feel free to comment or send me a message.
