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
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]
Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection [โฆ]
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 [โฆ]
How to create a dynamic pivot table and refresh automatically
This article shows you how to refresh a pivot table automatically using a small VBA macro. If you add or delete [โฆ]
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.
English
Spanish
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!