HI - I'm trying to create a pivot table that includes Start Date to group a select project task across all projects by Start Date. But when I export to excel, the field Start Date brings through the time too. Bringing through the time prevents the data from being grouped. Is there a modification I need to make to the Start Date field in Workfront to pull just the date in?
Solved! Go to Solution.
Views
Replies
Total Likes
Change the start date format in the report in the additional naming convention.
Or, change the format of the date in the excel tab.
When doing a pivot table, add as a data model it will let you group as year.
Views
Replies
Total Likes
Change the start date format in the report in the additional naming convention.
Or, change the format of the date in the excel tab.
When doing a pivot table, add as a data model it will let you group as year.
Views
Replies
Total Likes
Thank you so much for the suggestion! While I did change the format in excel before I posted this, it is not overwriting the inherited format with date. Per your suggestion, I did make the format date change in the report, but when it pulls through to excel, it still pulls the time in. I ended up making a hard format change in excel after that and it did remove the time. I was hoping the time would be excluded from the Workfront data though so I wouldn't need to reformat in excel.
Views
Replies
Total Likes
Hmm. In Advanced Options of the column header, it should allow you to choose to display the date or not and that should not export with your report. You could also create a calculated field that has the date iwthout the time but that's kind of redundant.
I found that when working in Excel - I need to 'clean' the data up before I start creating reports because it doesn't always retain the formatting.
Views
Replies
Total Likes
Hi @KathleenKi,
There's a special function that CLEARTIME that might do the trick for you.
To drop the time component, I suggest you switch the column of interest (e.g. Planned Start Date) to textmode, then change the definition so the row that was:
valuefield=project:plannedStartDate
to instead be:
valueexpression=CLEARTIME({project}.{plannedStartDate})
Once you save your view, load the data, and export to Excel, the plannedStartDate values should all be "at midnight", making your Pivot Table group "cleanly".
Regards,
Doug
TIP: if this solved your problem, I invite you to consider marking it as a Correct Answer to help others who might also find it of use
Views
Like
Replies