Workflow Calculated Field - DateDiff Struggle

I have a custom field “Last Activity Date” - that is set when a Call is Logged for a give Lead.
the Format is: 10/21/2022 06:00pm

I’m trying to Use the Workflow and "Calculated Field (unless there is a better way to accomplish) - to calculate and populate another Custom field “Days Since Last Activity”.

Seems like a simple DateDiff…however I’m getting unexpected results from my experiments:

I have tried a good few tests…but here is an example of strange output:

If I set the field formula to the {P0} wildcard set as the “Raw Value” of the “Last Activity Date” - I get “2,022” which seems to be the ‘Year’ portion of the date.

If I then try to force in the format parameter like this: {P0(m/d/Y h:ia)}
I get a “0” returned.

I “think” the formula I really want is: {datediff({P0}; {now(m/d/Y h:ia)}; days)}
But this yields a “0” as well.

The end result I’m looking for is bacically:

Last Activity Date - Today = “# of Days Since Last Activity Date”.

Any thoughts??

I just tried to the same with DateCreated (DateTime field) and the current date. This is my formula:

days since last activity: {datediff({now(d.m.Y)}; {date(d.m.Y;{P0})}; days)}

while {P0} is having the date_created-value.

the result:

it’s always a bit tricky with date(time) fields, you might have to play with the formats.

A more robust approach is to utilizie logic hooks instead, but this requieres a little code-work:

But another little hint: store this number in an integer field, that would allow you to filter for all accounts that have a value greater/lower than what you enter (“ranged search”).

1 Like

Thanks for the help!
As you described, I was able to force the format and create the correct calculation.