ToDate and DateDiff formulae not working in SF Compensation

[former_member126173]

Dear SF Compensation Experts,

In my template, we do have a requirement to do date difference between employee hire date and End Date (a constant date value). Hire date is taken from EC field ID: startDate.

When tried direct dateDiff between startDate and EndDate, the values appear as N/A. dateDiff(startDate,cust_ENDDATE).

formula: dateDiff(toDate(startDate,"MM/dd/yyyy"),toDate(cust_ENDDATE,"MM/dd/yyyy"))

Then, i created a custom column of type String and displayed startDate as toDate(startDate,"MM/dd/yyyy"). This logic also failed as it shows N/A.

Also, the dateDiff(toDate(CUST_HIREDATE,"MM/dd/yyyy"),toDate(cust_ENDDATE,"MM/dd/yyyy"))

Can someone advise on how this to be corrected where the CUST_HIREDATE should display hire date in MM/DD/YYYY, also the cust_ENDDATE is already in MM/DD/YYYY format?

[Former Member · Accepted solution]

ok, then I would try dateDiff(startDate, toDate(cust_ENDDATE,"MM/dd/yyyy")) or even dateDiff(startDate,toDate("08/31/2023","MM/dd/yyyy"))

[former_member126173 · Accepted solution]

Dear Philip, Thank you for your prompt responses!

Instead, i tried pulling hire date from EC into a custom field as string format and it worked and the datediff also worked.

[Former Member]

if startDate is a Date field, there is no need to put a "toDate" function on it.

I wonder most about the cust_ENDDATE column. What is that column's type? What is its contents? I would expect it to be a string field, maybe populated by a lookup table?

[former_member126173]

startDate is coming from EC, it's a standard column within the template. I assume it's date type.

cust_ENDDATE contains constant value '08/31/2023' of type string.