Forum Discussion

benz-fsproduce's avatar
benz-fsproduce
Frequent Visitor
4 years ago
Solved

Tenure Days

Hi, I am trying to calculate tenure days, in a new column, of employees based on my columns "last hire date" and "termination date". My current formula is: Tenure days = DATEDIFF(page[Last Hire Date], page[Termination Date], Day)

This formula only gives me the total tenure days of employees that have both a hire date and a termination date. So it is only counting the days of terminated employees. I want a formula that counts the tenure days of active employees as well. Thanks!

 

Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    benz-fsproduce , assume Termination Date is blank for active employess

     

    Tenure days = DATEDIFF(page[Last Hire Date], coalesce(page[Termination Date], Today()) , Day)

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    benz-fsproduce , assume Termination Date is blank for active employess

     

    Tenure days = DATEDIFF(page[Last Hire Date], coalesce(page[Termination Date], Today()) , Day)

  • HotChilli's avatar
    HotChilli
    Community Champion
    4 years ago

    You could create a variable (something like var1 = COALESCE ((terminationD, TODAY()) )

    and then use the variable in the datediff.

    I think COALESCE works in this context, if not you can explicitly check for a null or blank termination date .