Forum Discussion

DjDon's avatar
DjDon
Frequent Visitor
5 years ago

Days between dates with nulls and blanks

Hello all,

I have a table with three columns:  Seniority date, Hire date and data date.

 

I'd like to calculate the days/years between Seniority date and data date, however, the Seniority date column contains blanks and nulls along with actual dates.

 

If a given line contains either a blank or null for seniority date, then I want to look at Hire date and apply the same logic.  If Hire date contains a blank or null, then return a blank in the field for SenYrs. 

 

The below attempt at the code says there are no syntax errors but I get an error at runtime stating "The name 'isblank' wasn't recognized'.  Any help or ideas would be appreciated.

 

if(not(isblank[Seniority Date])) or  

  [Seniority Date]<>null then 

   ([data date]-[Seniority Date])/365.25

else 

 if(not(isblank[Hire Date])) or  

  [Hire Date]<>null then 

   ([data date]-[Hire Date])/365.25

else 

 blank()

Days

2 Replies

  • rfigtree's avatar
    rfigtree
    Resolver III
    5 years ago

    your missing a bracket

     

    if(not(isblank  (  [Seniority Date])) or  

  • DjDon's avatar
    DjDon
    Frequent Visitor
    5 years ago

    Appreciate the response, however, I still get the same error.  I did try to rewrite the code but now am getting a "Token Then expected" error ar runtime:

     

    if [Seniority Date] <> null || [Seniority Date] <> ""
    then
    ([data date]-[Seniority Date])/365.25
    else if [Hire Date] <> null || [Hire Date] <> ""
    then
    ([data date]-[Hire Date])/365.25
    else
    blank()