trying to use datedif function

%3CLINGO-SUB%20id%3D%22lingo-sub-1330205%22%20slang%3D%22en-US%22%3Etrying%20to%20use%20datedif%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1330205%22%20slang%3D%22en-US%22%3E%3CP%3Etrying%20to%20subtract%20two%20dates%2C%20hire%20date%20and%20termination%20date%20to%20get%20employee%20tenure.%26nbsp%3B%3C%2FP%3E%3CP%3EHire%20date%20has%20a%20vlookup%20function%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(ISERROR(VLOOKUP(A2%2Cstaff!A%3AH%2C8%2CFALSE))%2C%22Not%20hired%22%2C(VLOOKUP(A2%2Cstaff!A%3AH%2C8%2CFALSE)))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethis%20function%20returns%20hire%20dates%20if%20the%20candidate%20referred%20is%20found%2C%20if%20not%20%22Not%20hired%22%3C%2FP%3E%3CP%3EAlmost%20the%20same%20for%20the%20termination%20date%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(ISBLANK(VLOOKUP(A2%2Cstaff!A%3AH%2C7%2CFALSE))%2C%20TODAY()%2C(VLOOKUP(A2%2Cstaff!A%3AH%2C7%2CFALSE)))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ewhich%20returns%20todays%20date%20if%20the%20referenced%20data%20does%20not%20have%20a%20date%20in%20the%20field%20as%20they%20are%20still%20current%20employees.....%20How%20can%20I%20get%20my%20tenure%20column%20to%20work%3F%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(ISERROR(DATEDIF(E57%2CF57%2C%22D%22))%2C%220%22%2C(DATEDIF(E57%2CF57%2C%22D%22)))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20partially%20working%2C%20but%20not%20in%20entirety...%20Thank%20you%20in%20advance!%26nbsp%3B%3C%2FP%3E%3CP%3E(my%20first%20time%20ever%20doing%20this%20and%20my%20first%20time%20in%20this%20group..yay%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1330205%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1330222%22%20slang%3D%22en-US%22%3ERe%3A%20trying%20to%20use%20datedif%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1330222%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F633293%22%20target%3D%22_blank%22%3E%40lulbabyada%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIFERROR(F57-E57%2C0)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1330284%22%20slang%3D%22en-US%22%3ERe%3A%20trying%20to%20use%20datedif%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1330284%22%20slang%3D%22en-US%22%3E%3CP%3EhI%20Sergei%20thanks%2C%20that%20worked%20for%20the%20fields%20that%20had%20both%20a%20hire%20date%20and%20a%20term%20date%2C%20but%20what%20about%20the%20ones%20that%20produced%200%20due%20to%20having%20%22Active%22%20as%20text%20in%20the%20term%20date%20field%20or%20%22Not%20hired%22%20in%20the%20hire%20date%20field%3F%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1330302%22%20slang%3D%22en-US%22%3ERe%3A%20trying%20to%20use%20datedif%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1330302%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F633293%22%20target%3D%22_blank%22%3E%40lulbabyada%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20didn't%20find%20%22Active%22%20first%20time%2C%20my%20guess%20was%20it%20shall%20be%20TODAY().%20With%20that%20it%20could%20be%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20450px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F185988i46EA177DDDB68C5C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EPerhaps%20you%20may%20give%20possible%20combinations%20of%20dates%20and%20texts%2C%20it's%20bit%20hard%20to%20make%20a%20guess%20what%20VLOOKUP()%3As%20shall%20return.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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

5 Replies
Highlighted

@lulbabyada 

Perhaps

=IFERROR(F57-E57,0)
Highlighted

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 

Highlighted

@lulbabyada 

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

image.png

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

Highlighted

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

Screen Shot 2020-04-22 at 5.32.04 PM.png

@Sergei Baklan 

Highlighted

@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

image.png