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 followi...
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

     

     

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