Forum Discussion

lulbabyada's avatar
lulbabyada
Copper Contributor
Apr 22, 2020

trying to use datedif function

trying to subtract two dates, hire date and termination date to get employee tenure. 

Hire date has a vlookup function 

 

=IF(ISERROR(VLOOKUP(A2,staff!A:H,8,FALSE)),"Not hired",(VLOOKUP(A2,staff!A:H,8,FALSE)))

 

this function returns hire dates if the candidate referred is found, if not "Not hired"

Almost the same for the termination date:

 

=IF(ISBLANK(VLOOKUP(A2,staff!A:H,7,FALSE)), TODAY(),(VLOOKUP(A2,staff!A:H,7,FALSE)))

 

which returns todays date if the referenced data does not have a date in the field as they are still current employees..... How can I get my tenure column to work?:

 

=IF(ISERROR(DATEDIF(E57,F57,"D")),"0",(DATEDIF(E57,F57,"D")))

 

It partially working, but not in entirety... Thank you in advance! 

(my first time ever doing this and my first time in this group..yay

excel
Excel on Mac
Formulas and Functions

5 Replies

    • lulbabyada's avatar
      lulbabyada
      Copper Contributor
      Apr 22, 2020

      hI Sergei thanks, that worked for the fields that had both a hire date and a term date, but what about the ones that produced 0 due to having "Active" as text in the term date field or "Not hired" in the hire date field? SergeiBaklan 

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Apr 22, 2020

        lulbabyada 

        I didn't find "Active" first time, my guess was it shall be TODAY(). With that it could be

        Perhaps you may give possible combinations of dates and texts, it's bit hard to make a guess what VLOOKUP():s shall return. 

Resources