-1

I have one scenario where I have to display cases in report whose lastmodifieddate is greater than closeddate So everything is working fine but there is one issue I have a case and I closed it. In my salesforce backend the closeddate is 2022-01-17T10:04:50.000+0000 and the lastmodifieddate is 2022-01-17T10:04:51.000+0000

It is giving me 1-sec difference which fulfilling my condition but it's wrong. So I want to compare these two fields without involving seconds in report. Anyone has any idea please let me know. Thanks in advance.

Improve this question

3 Answers 3

Reset to default
1

I have create the custom formula and has solved the problem.

IF(
    AND(
        DATEVALUE(LAST_UPDATEONLY) == DATEVALUE(CLOSED_DATEONLY),
        HOUR(TIMEVALUE(LAST_UPDATEONLY)) == HOUR(TIMEVALUE(CLOSED_DATEONLY)),
        MINUTE(TIMEVALUE(LAST_UPDATEONLY)) == MINUTE(TIMEVALUE(CLOSED_DATEONLY)),
        SECOND(TIMEVALUE(LAST_UPDATEONLY)) != SECOND(TIMEVALUE(CLOSED_DATEONLY))
    ), 
    1 , 
    0
)
Improve this answer
1
  • If you made this a checkbox (formula) field, I imagine it (and your report filters) could be simplified. That'd allow you to remove the IF() entirely, and remove the comparison against the seconds. The formula would end up telling you if the datetimes are the same down to the minute, and your report filter would simply be "is this formula field true".
    – Derek F
    Jan 17, 2022 at 14:15
0

Since you are comparing two "datetime" fields in your report, it is taking into consideration the time. Way to get around this will be to compare 2 date fields instead. Since lastmodifieddate is a datetime field, you can use a formula to only store the date and then compare that with your closedate field.

Improve this answer
1
  • I've downvoted this because I think it misses the mark. OP wants to still compare the time portion, just without the "seconds" bit. So if both datetimes have a time portion of 10:40 (10:40:50 and 10:40:51), they should be treated as the "same time".
    – Derek F
    Jan 17, 2022 at 12:22
0

The one case that I can see where your (Abishek Saxena) answer won't work is if you experience this 1 second difference between LastModifiedDate and ClosedDate on a minute boundary.

A difference between those two fields should be rare enough as is, and a difference across a minute boundary should be rarer still. So with that in mind, it may not make sense to put more effort into this.

However, I think there's a simpler approach that will end up being more accurate.

Formulas allow us to add/subtract with datetimes, so if you simply subtract your two datetime fields and check to see if the difference is greater than some threshold you should end up with what you want.

(LastModifiedDate - ClosedDate) > 0.0000116

Subtracting datetimes gives you a decimal result, and is based on the number of seconds between the two datetimes.
Whole number part = number of complete days in the difference
Decimal part = remaining number of seconds / 86400 (the number of seconds in a day)

So if you want to find if your two datetimes are within X seconds of eachother, the number you want to compare against is X / 86400, for 1 second that's roughly 0.00001157407.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .