Calculated field returns an error

[Annab]

Hi,

I have created calculated field with the following logic:
parseDate({Status_change_date_time__c_his}, “yyyy-MM-dd HH:mm:ss”)
And it work corretly.

Than I added to it the following (in order to change it from a date time field into a date only field):
formatDate(parseDate({Status_change_date_time__c_his}, “yyyy-MM-dd HH:mm:ss”),“dd/mm/yyyy”)

And it returned the following error:
“Your function expression contains an unsupported date. Correct the date format and try again.”

What am I doing wrong?

[wstevens01]

Hi @Annab ,

This fails because parseDate() takes a string as an expression, not the date field that you have. See here.

The best way to show just the date is using the custom format in the field well. Go to the field well and select Format, then More formatting options:

Then in format panel, select custom and find a format string that you like:

See here for different options.

Hope that helps.

ws

[Annab]

Thank you @wstevens01

The thing is that I was planning to use it in the Datediff function.
Any idea how to do so?

[wstevens01]

Hi @Annab ,

DateDiff() does take two dates and a datepart. See docs here.

So for your use-case, you should only need the parseDate function, e.g.:

datediff(day, parseDate(…), parseDate(…) )

That should produce an integer result, without having to limit the parseDate result to a day.

Let me know how that works for you.

ws

[Annab]

Hi @wstevens01 ,

It didn’t work.
So I went back to the data set and implemented there the format changes and than it worked.

[debapc]

Hi @Annab,

I was following this thread. It’s great to hear that your issue is resolved by addressing the format changes at the dataset level. However, I wonder why it didn’t work with parsedate and datediff functions. I did a quick POC and the outcome was successful. Here are the details:

1. I created the following data set. Pls note that OrderDate and ShipDate are strings:

2. I created 2 calculated fields and parsed the OrderDate and ShipDate:

3. I created another calculated field to calculate the Date difference:

4. Finally I published the data as a table visual:

Not sure whether it is helpful at this stage. However thought to share the details. Thanks!