How to Create a Time Tracker in Google Sheets

Building a time tracker in Google Sheets can be the first step toward boosting your productivity, managing projects more effectively, and ensuring accurate client billing. Forget complicated software - you can create a surprisingly powerful and customized system using a tool you already know. This guide will walk you through a step-by-step process, from creating a simple time log to building an automated dashboard that visualizes your efforts.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Setting Up Your Basic Time Tracking Sheet

Let's build the foundation of our tracker. This involves creating a simple table to log your activities manually. It’s the simplest way to get started and provides the structure we'll build on later.

Step 1: Create Your Columns

Open a new Google Sheet and dedicate the first row to your headers. For a basic but effective tracker, you’ll need the following columns:

• Date: The date the work was performed.
• Project: The name of the client or high-level project.
• Task: A specific description of the work done (e.g., "Wrote blog post draft," "Designed homepage mockups").
• Start Time: The time you started the task.
• End Time: The time you stopped the task.
• Total Hours: This is where our first formula will go to calculate the duration.

Step 2: Format Your Columns for Easy Data Entry

Proper formatting prevents errors and makes the sheet much easier to read. Right now, your Google Sheet sees a time like "9:30 AM" as a fraction of a day, which can complicate calculations if not handled correctly. Let's fix that.

1. Select the entire "Date" column by clicking the column letter (A). Go to Format > Number > Date.
2. Select the "Start Time" and "End Time" columns (D and E). Go to Format > Number > Time.
3. Select the "Total Hours" column (F). Go to Format > Number > Number. This ensures our calculated duration appears as a decimal instead of a time format.

Your sheet should now look clean and organized, ready for data. But manually typing project names or times can be tedious. Let's start adding some automation.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Automating Hours and Costs with Formulas

Now it's time for Google Sheets to do the heavy lifting. Instead of using a calculator, we’ll use simple formulas to calculate durations and billable amounts automatically.

Calculating Total Hours Worked

This is the most important formula in our time tracker. Click into cell F2 (the first cell under your "Total Hours" header) and paste the following formula:

=(E2-D2)*24

Let's break that down:

• E2-D2 calculates the difference between the end and start times. Google Sheets handles this by converting the times to their numeric value (a fraction of a 24-hour day).
• *24 converts that fractional value into a more friendly decimal number of hours. Without this, a result for 8 hours of work would show up as "8:00:00". Multiplying by 24 gets us the easy-to-read "8".

After entering the formula in F2, grab the small blue square (the "fill handle") in the bottom-right corner of the cell and drag it down. This will copy the formula to every row, automatically adjusting the cell references.

Adding Billable Rates and Total Costs

If you're using this for client work, let's add two more columns: Hourly Rate and Total Billable.

First, format the "Hourly Rate" and "Total Billable" columns as currency by selecting them and going to Format > Number > Currency.

In the first cell under "Total Billable" (let's say it's H2), enter this simple formula:

=F2*G2

This formula multiplies the Total Hours (F2) by the Hourly Rate (G2) to give you the amount to bill for that specific task. Drag the fill handle down to apply this formula to your entire sheet. Now, whenever you log time and an hourly rate, the billable amount appears instantly.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Streamlining Your Workflow with Dropdown Menus

As you log more entries, typos happen. Inconsistent entries like "Project A" vs. "project alpha" can make summarizing your data a nightmare. Dropdown menus enforce consistency and speed up your data entry process.

Let's create a dropdown for the "Project" column.

1. First, create a new tab in your sheet by clicking the '+' icon at the bottom. Name it "Lists".
2. In this "Lists" tab, in column A, type out all your current project names, one per cell (e.g., A1: Project A, A2: Project B, A3: Client X).
3. Navigate back to your main time tracking sheet. Select the entire "Project" column.
4. Go to the menu and select Data > Data validation.
5. In the popup menu, for "Criteria", choose "Dropdown (from a range)".
6. Click the small grid icon next to the text box and select the range from your "Lists" tab (e.g., Lists!A:A).
7. Make sure "Show dropdown list in cell" is checked and click "Save".

Now, when you click on any cell in the "Project" column, a handy dropdown menu will appear with your predefined list. No more typos!

Creating a Summary Dashboard to Analyze Your Time

Logging your time is useful, but seeing the bigger picture is where the real value lies. Let's create a separate tab that acts as a live dashboard, summarizing where all your hours are going.

Create a third tab and name it "Dashboard".

Summarize Hours Per Project with SUMIF

On your new Dashboard tab, create a simple table with two columns: "Project" and "Total Hours". In the "Project" column, list all the projects you want to track - the same names from your "Lists" tab.

To calculate the total hours for the first project in your list (let's say its name is in cell A2 of your Dashboard tab), click into B2 and enter this formula:

=SUMIF('Sheet1'!B:B, A2, 'Sheet1'!F:F)

Let's translate that into plain English:

• 'Sheet1'!B:B: This is the range you want to check. It's the "Project" column on your main time tracker sheet (assuming your tracker is named 'Sheet1').
• A2: This is the condition. The formula is telling Google Sheets, "Only sum the numbers if the project name in Sheet1 matches the name in cell A2 of this dashboard."
• 'Sheet1'!F:F: This is the range you want to add up. It's the "Total Hours" column on your main tracker.

Once again, drag the fill handle down to apply this formula to all your projects. Now you have a clean, auto-updating summary of total time spent on each project.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Visualize Your Time with a Pie Chart

A visual representation can be much quicker to digest than a table of numbers. Let's add a chart to our dashboard.

1. On your "Dashboard" tab, select your summary data (both the "Project" and "Total Hours" columns).
2. Go to Insert > Chart.
3. Google Sheets will often default to a pie chart, which is perfect for this. If it doesn't, go to the "Setup" tab in the Chart editor on the right and select "Pie chart" from the Chart type dropdown.
4. You can customize the colors, titles, and labels in the "Customize" tab of the editor.

Now you have a dynamic pie chart that visualizes exactly where your time is being allocated. It updates automatically as you log new entries in your main sheet.

Final Thoughts

With just a handful of columns, a few smart formulas, and some basic visualization, you've successfully built a comprehensive time tracking system in Google Sheets. This system not only captures what you're working on but also provides automated calculations and high-level insights to help you understand and optimize your time distribution.

As you get more comfortable managing data in spreadsheets, you'll find that connecting data across different tools - like pulling performance metrics from your ad platforms to compare against project hours - is the next big hurdle. This is where we built Graphed to simplify things. Instead of spending hours wrangling data from multiple CSV files, we let you connect all your business platforms in seconds and build real-time, interactive dashboards just by asking questions in plain English - no formulas required.