Date Difference

4 followers
0
Avatar

I have two date fields (date1 and date2) currently with a string data type.  I'm trying to find the number of days between the two dates. There are some instances where date2 is blank. 

When I add a column with the below formula I receive the correct results, however when I execute reload there are a number of warnings saying "unable to parse date"

IF(NOT(ISBLANK(#date1));(subtract(ASDATE(#date2);ASDATE(#date1))/1d);null)

How can I remove these warning messages? 

Peter Han-Grassman

7 comments

  • Avatar
    Venu Reddy

    Hi Peter,

    Since you mentioned that date2 could be blank, shouldn't the formula check against date2 instead?

    Try with:

    IF(NOT(ISBLANK(#date2)); subtract(ASDATE(#date2);ASDATE(#date1))/1d);null)

    Also, once you convert strings to dates, you can do subtraction using the minus operator:

    IF(NOT(ISBLANK(#date2));((ASDATE(#date2)-ASDATE(#date1))/1d);null)

    0
  • Avatar
    Peter Han-Grassman

    Venu, Sorry, date1 can be blank.  Date2 will always be populated.  Any thoughts with that use case?

    0
  • Avatar
    Joel Stewart

    I was able to successfully use your original formula and execute then re-open the workbook successfully. I'm using Datameer 5.11.14, what version did you test with? 

    There may be some additional context to the error message in the conductor.log file: https://datameer.zendesk.com/hc/en-us/articles/204557494

    Does the Workbook execution complete successfully or does it complete with warnings? 

    0
  • Avatar
    Joel Stewart

    Since it completes with warnings, there is likely some details in the Job Trace that may point to the cause in more detail (i.e. with a stack trace).

    I'd recommend collecting and reviewing the Job Trace: https://datameer.zendesk.com/hc/en-us/articles/204802160

    If you'd like assistance with reviewing the Job Trace, I'd recommend creating a ticket with our dedicated Support team and attaching the Job Trace to troubleshoot this specific occurrence. 

    0
Please sign in to leave a comment.