Multi-row formula

Question

Hi all, I have a data set with column "Ship Date" which is in "%Y-%m-%d" format. I need to perform the following using a multi-row formula

1. Convert column to date format : ToDate([Ship Date])

2. Convert Ship Date to desired format : DateTimeFormat([Company], "%m/%d/%Y")

3. Find difference in date between Ship date and current date grouped by company : DateTimeDiff(DateTimeToday(), [Ship Date], "days")

The output column is shown as difference. Can someone please suggest where my formulas are going wrong? I am getting error with converting the "Ship Date" using the formula. Appreciate if someone can tell me how to wrap these three formulas in one line to avoid multiple tiles - preferabbly using "Multi-row formula" tool.

Input (all columns up to ship date) & output is Difference

Order IDSegmentCategoryCompanyShip DateDifferenceCA-2017-122765Home OfficeFurnitureA ltd.2017-03-280CA-2017-122771CorporateFurnitureA ltd.2017-07-0296CA-2017-122774CorporateFurnitureA ltd.2017-10-0292CA-2017-122763ConsumerOffice SuppliesB ltd.2017-03-200CA-2017-122768CorporateOffice SuppliesB ltd.2017-05-2566CA-2017-122770ConsumerOffice SuppliesB ltd.2017-06-1521CA-2017-122772CorporateOffice SuppliesB ltd.2017-08-1157CA-2017-122775ConsumerOffice SuppliesB ltd.2017-10-0555CA-2017-122764ConsumerTechnologyC ltd.2017-03-250CA-2017-122766ConsumerTechnologyC ltd.2017-04-2329CA-2017-122767CorporateTechnologyC ltd.2017-04-296CA-2017-122769ConsumerTechnologyC ltd.2017-05-2627CA-2017-122773CorporateTechnologyC ltd.2017-09-13110

DataNath · Answer

@Mj9715 how does this look? You'll need to create a new field for the date format you're after as your date is already in standard date form and you need to change it to a string and use the datetimeformat() function for the MM/DD/YYYY you're after. You also used [Company] in your date calc which would have definitely thrown it off. You can add more than one expression in the formula tool and so I'd just handle them both in one and then use the select to remove your old date field that isn't formatted properly:

Ship date format and difference.yxmd

Emmanuel_G · Answer

@Mj9715

Step 2 : You applied DateTimeFormat to Company field.

Another thing I could advise you is to reverse steps 2 and 3. Because when you apply a DateTimeFormat to a date, it becomes of type string. And with in DateTimeDiff, both fields must be dates. Which is not the case if we apply the DateTimeformat before making the difference between the dates.

Finally, in my opinion, no need to apply the DateTimeformat in the multi-row formula. It is better to do it in a Multi-field or formula tool.