trying to use datedif function

[lulbabyada]

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

[#2]

@lulbabyada 

Perhaps

=IFERROR(F57-E57,0)

[#3]

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? @Sergei Baklan 

[#4]

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

[#5]

That is my data sheet, I need function for termination date that will show when they were terminated but if the field is blank due to no termination yet I need it to say todays date so that I can subtract term date and hire date to get tenure column to have the date difference

 

@Sergei Baklan 

[#6]

@lulbabyada 

There are different VLOOKUP formulas in your first post and on screenshot, not sure which one you use. Anyway, let assume in hire date column cells could be two variants of values - text  or date. Actually the same is term date column, the only difference that could be here Term Date or TODAY() or text.

Plus for some cells we have Jan 00, 1900, or zero - VLOOKUP() returns value of the blank cell.

IMHO, formula works for all such combinations