Changing Date Fields so they can be filtered in Excel and Fixing Currency Fields for Excel

[TinaLong]

Good morning, Community!

I'm pulling a CSV file into a workflow.

1. I can never get the date fields right.  My date field come in as a string. When I export it out to Excel for team members, I need it to be a true date field (MM/DD/YYY) they can filter and sort on.

2.   This file also contains currency fields, but the data come in like this (pls see below).  How can I add $ to the Amount and no cents ($150)?  And for the ranges, I just want to make them right margin flush.

All suggestions are welcomed!

[chandler-gjino · Accepted solution]

you can use the datetime tool found in the parse palette to convert string dates to date dates and vice versa 
you can also use the datetimeparse function inside a formula tool

for the currency to add a $ -- I used a formula tool as well to concatenate the $. can also use the round function to remove any decimals 

attaching a sample workflow

[Prometheus]

@TinaLong You can use the DateTime tool to change your date field into an actual date field, then a Select tool to deselect the original date field and change the resulting DateTime_Out to "Date 1" and change data types of the Amount fields to String. Then you can use the Multi-Field Formula tool to add "$" to the beginning of the Amounts with this expression: '$'+[_CurrentField_]. I used a Table tool to justify the fields to the right and a Render tool to render the report.

 

 

[TinaLong]

Hi,

Thank you!  The currency solution was perfect (and so easy, LOL - I'm still learning). 

Regarding the dates, I was hoping for the export to end up like below.  Is that possible?  If not, I can try to get in this format in Excel after I complete the output.  But let me know. Thanks