Forum Discussion

fernandoC's avatar
fernandoC
Helper V
5 years ago
Solved

Date Diff Formula

Hi!,

 

I'm trying to obtain the Time to Hire for a specific dataset which is the date difference between the contact date of a candidate and the resolution of the position. I've created the following formula, however, I receive an error message: An Argument of the function 'Date' has the wrong data type or the result is too big or too small. 

Time to Hire =
VAR Iscandidate = CALCULATE(COUNTROWS(FILTER('Main Jira Info','Main Jira Info'[Status Name] = "Candidate")))
VAR Candidatedate = IF(Iscandidate,DATE('Main Jira Info'[CREATED Date].[Day],0,0),BLANK())
VAR Ishired = CALCULATE(COUNTROWS(FILTER('Main Jira Info','Main Jira Info'[Requisition Decision] = "Filled")))
VAR Hiredate = IF(Ishired,DATE('Main Jira Info'[RESOLUTIONDATE].[Day],0,0),BLANK())
RETURN DATEDIFF(Candidatedate,Hiredate,DAY)
 
Any suggestions?.
 
Thanks in advance.
 
Best, 
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi fernandoC ,

    The error is caused by the two dates quoted(see the part with red circle) do not match the date format...You can refer this link about the usage of Date function.

    First, please make sure the data type of field 'Main Jira Info'[CREATED Date] and 'Main Jira Info'[Requisition Decision] are Date, then update the formula of calculated column [Time to Hire] as below:

    Time to Hire =
VAR Candidatedate =
    IF (
        'Main Jira Info'[Status Name] = "Candidate",
        'Main Jira Info'[CREATED Date],
        BLANK ()
    )
VAR Hiredate =
    IF (
        'Main Jira Info'[Requisition Decision] = "Filled",
        'Main Jira Info'[RESOLUTIONDATE],
        BLANK ()
    )
RETURN
    DATEDIFF ( Candidatedate, Hiredate, DAY )​

    If the above method doesn't work, please provide some sample data in table Main Jira Info and explain what you want. Thank you.

    Best Regards

    Rena

     

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    fernandoC , .day is used

    Time to Hire =
    VAR Iscandidate = CALCULATE(COUNTROWS(FILTER('Main Jira Info','Main Jira Info'[Status Name] = "Candidate")))
    VAR Candidatedate = IF(Iscandidate,DATE('Main Jira Info'[CREATED Date].Date,0,0),BLANK())
    VAR Ishired = CALCULATE(COUNTROWS(FILTER('Main Jira Info','Main Jira Info'[Requisition Decision] = "Filled")))
    VAR Hiredate = IF(Ishired,DATE('Main Jira Info'[RESOLUTIONDATE].Date,0,0),BLANK())
    RETURN DATEDIFF(Candidatedate,Hiredate,DAY)

     

    Also if the date does not have a timestamp. then you avoid .date

    • fernandoC's avatar
      fernandoC
      Helper V
      5 years ago

      Hi amitchandak ,

       

      Thank you for your help!.

       

      I've updated the formula  as you mentioned but I'm still receiving the same error message: 

       

       

      Also tried to remove the .[Date] as the columns being referenced only have date but not a timestamp but still receive the same error message. 

       

      Best, 

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi fernandoC ,

        The error is caused by the two dates quoted(see the part with red circle) do not match the date format...You can refer this link about the usage of Date function.

        First, please make sure the data type of field 'Main Jira Info'[CREATED Date] and 'Main Jira Info'[Requisition Decision] are Date, then update the formula of calculated column [Time to Hire] as below:

        Time to Hire =
VAR Candidatedate =
    IF (
        'Main Jira Info'[Status Name] = "Candidate",
        'Main Jira Info'[CREATED Date],
        BLANK ()
    )
VAR Hiredate =
    IF (
        'Main Jira Info'[Requisition Decision] = "Filled",
        'Main Jira Info'[RESOLUTIONDATE],
        BLANK ()
    )
RETURN
    DATEDIFF ( Candidatedate, Hiredate, DAY )​

        If the above method doesn't work, please provide some sample data in table Main Jira Info and explain what you want. Thank you.

        Best Regards

        Rena