I need to compare if a certain date/time field is greater than/less than another date field. Can this be done in a report without calculating the same using formula/boolean fields?


Don't think so. You'll need a formula field, either direct one or something set with a workflow (or Apex code of course). If that's not feasible and conditions will change often - VF page embedded on a dashboard might be the way to go?

All our filters (in WHERE clauses, reports, listviews etc) compare field to value, not field to field. There are exceptions like lookup filters but that's not helping much.

You won't be able to pull it off with a formula built in the report itself (it'd be interesting to say LastModifiedDate:MAX - Custom_Date_Field__c:MAX and use grouping by Id to have 1 record and thus cheat with this MAX clause). But date & datetime fields can't be used in summary formulas. More info in the summary formula help.

  • You're saying because it's a date/time field the two can't be compared? – crmprogdev May 12 '13 at 19:16
  • 1
    In formula field (on the object) they can be compared because it makes sense to say "point in time minus another point in time = 3 days 7 hours". Even then - you'll probably agree that calculation "date + another date" makes no sense. With report-level formulas it's even worse because they will show for many rows at the same time and on grand total level for example. "Max date" makes sense but for all the rest SF decided they won't expose date fields, period. – eyescream May 12 '13 at 19:23
  • All good points eyescream. I was thinking along the lines of adding a custom cross-block formula with a "days between" or "hours between" type of formula approach. Admittedly, I've never had the need to try it. Will add that to my "to try" list. ;) – crmprogdev May 12 '13 at 19:28
  • 1
    Sadly it's a quite common requirement, from reminder kind of reports "days till the contract will expire / has to be extended" to team performance "how long was the Case open". There's no nice way for these :/ There are workarounds through formula fields but as the link to help says - usage of any kind of formula field with IF etc will slow the report and of course you're wasting a field... – eyescream May 12 '13 at 19:33
  • Thanks eyescream. Learned something new today. The "how long was open" is exactly one of the things that came to mind because it's one of the metrics included as part of the tutorial in the Platform Fundamentals Workbook. They did a grtr than no days in their formula as I recall but also stored the actual days between Open and Close in a separate field. – crmprogdev May 12 '13 at 19:52

Salesforce is coming with this new feature soon.

Refer for more details https://success.salesforce.com/ideaview?id=08730000000BrHAAA0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.