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 for 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
- 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 time sheet by project with overtime hours.