Skip to main content

Invalid operation error for the DATEDIF formula in reports

  • October 19, 2023
  • 6 replies
  • 146 views
R
Bobber

The system requires unique values for the DATEDIF formula to function correctly.

When calculating the time difference between two dates, each date must be distinct. If one of the dates is a historical value (such as a lifecycle status or effective date from history), the system may not have unique dates to reference, which can cause an error.

  • To avoid this, ensure that both dates used in the DATEDIF formula are unique and not repeated elsewhere in the dataset.

6 replies

H
  • HannahB
  • April 3, 2024

what if I want one of those dates to be today, should it error?

R
Bobber
  • Roque
  • Author
  • Bobber
  • May 13, 2024

Hi Hannah! The system should be able to calculate a number based on two dates in the system. In this case, yes you can use a day that can match today’s date (termination date falls on today’s date). Note that the data must come from a date field in the system such as start date, termination date, etc. 

    C

    is it possible for bob to calculate based on a custom date rather than a date in the system? i’m getting an error when trying to DATEDIF(31/03/2024, [Termination date], “D”) is this because the formula can only support field-based dates?

    Maital Terdiman
    Bobber

    Hi ​@Charlie Ensor 

    You’ve date-initely spotted the issue! 😉

    At the moment, the formula feature is a bit picky — it can only tango with field-based dates in the system, not hardcoded/custom ones like 31/03/2024. 

    Hopefully we’ll see more flexi-date functionality for the formulas feature in the future! ⏳

     

    Bobbing along as best as one can bob
      P
      • Palm Niina
      • February 3, 2026

      ​@Maital Terdiman any plans to enable this at some point? We often need payroll reports to calculate salaries paid within a certain period (typically calendar year) but currently we can only get a report that calculates salaries paid between effective dates. Just a simple report that lists all salaries paid within given time period would be perfect 😍

        D

        ​@Maital Terdiman Same question as above! Would be great to have the equivalent of the excel formula DATE(YYYY, MM, DD) to be able to use it in the reports. 

        Terms & ConditionsAccessibility statement