Date Diff Formula

[fernandoC]

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, 

[amitchandak]

@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]

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 · Accepted solution]

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