Start Date in Excel without the Time

[KathleenKi]

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?

[Christina_Jarosz · Accepted solution]

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. 

[KathleenKi]

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.

[Christina_Jarosz]

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. 

[Doug_Den_Hoed__AtAppStore]

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