Last week, we talked about using the Odata Timeset table to create time phased reports in Excel:
Project Online - Time Phased Reporting in Excel - INTEGENT BLOG
We noted the Timeset table provides Week number, Month number and Quarter number for all dates between 1984 and 2049. However, our data often extends across multiple years. And when that happens, we need to add a column for Year.
To do this, we will use PowerPivot. For this post, we will assume you have PowerPivot installed in Excel.
Open your Timeset table in Powerpivot. In the first empty column on the right end of the spreadsheet, add this calculation to the column:
=YEAR([TimeByDay])
PowerPivot will automatically apply this formula to every cell in the column. Right click on the column head and change it to Year.
Now, when you apply your time phased information to a table or a chart, your weeks and months will understand what year they belong in, as shown below:
For more tips, explore other PowerPivot blog posts on our site:
