Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

SOLVED

Start Date in Excel without the Time

Avatar

Level 2

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?

1 Accepted Solution

Avatar

Correct answer by
Level 9

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. 

View solution in original post

4 Replies

Avatar

Correct answer by
Level 9

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. 

Avatar

Level 2

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.

Avatar

Level 9

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. 

Avatar

Level 10

 

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