Create monthly time sheet using a Pivot Table
Today I am going to demonstrate how amazing pivot tables are! Take a look at this time sheet.
You can expand each month and see time spend on each project. The pivot table also shows a summary of both months and projects.
I made the pivot table from a simple table containing fake project time data.
This table can have duplicate dates, as you may be working on multiple projects in a single day.
How I created the Pivot Table
- Select a cell in your table.
- Go to tab "Insert".
- Click "Pivot table" button.
- Click OK.
Arrange data
- Click and drag "Day" from "Pivot table Field List" to "Column Labels" area.
- Resize pivot table columns widths.
- Click and drag "Date" to "Row Labels" area.
- Right-click on a date and select "Group".
- Select months and click OK.
- Click and drag "Project" to "Row Labels" area.
- Click and drag "Regular hours" to "Values" area.
- Go to tab "Design".
- Click "Subtotals" button.
- Click "Show all subtotals at bottom of group".
- Click and drag overtime hours to "Values" area.
- Change headers "Sum of Regular hours" to R and "Sum of overtime hours" to O.
Picture of a monthly timesheet by project with overtime hours.
Recommended articles
Weekly Blog EMAIL
Welcome! I am Oscar and here to help you out.Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
Related articles
How to use an Excel Table name in Data Validation Lists and Conditional Formatting formulas
This article demonstrates different ways to reference an Excel defined Table in a drop-down list and Conditional Formatting. There are […]
Extract unique distinct values from a filtered Excel defined Table [UDF and Formula]
This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]
Count unique distinct values in a filtered Excel defined Table
This article demonstrates a formula that counts unique distinct values filtered from an Excel defined Table. Debra Dalgleish described in […]
In a previous post: How to create a dynamic pivot table and refresh automatically I demonstrated how to refresh a pivot […]
Use hyperlinks in a pivot table
Sean asks: Basically, when I do a refresh of the data in the "pivotdata" worksheet, I need it to recognise […]
Change PivotTable data source using a drop-down list
In this article, I am going to show you how to quickly change Pivot Table data source using a drop-down […]
Tesh asks: How easy is it to modify this for recurring tasks (weekdays, weekly, monthly, quarterly and yearly) and maybe […]
Plot date ranges in a calendar part 2
I will in this article demonstrate a calendar that automatically highlights dates based on date ranges, the calendar populates names […]
Geoff asks: Hi Oscar, I have a cross reference table we use for shift scheduling. The x-axis is comprised of […]
2 Responses to “Create monthly time sheet using a Pivot Table”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Oscar,
I've been experimenting with various formats (in Excel ofcourse) for tracking my time, but have never been totally satisfied. This PivotTable might just be the ticket! Thanks for sharring!
Cheers, Lukas
Lukas,
I am happy you like it!