How to remove #NUM from DATEDIF negative formula

New Contributor

Hi, I have a quick query re DATEDIF. I am using the function to calculate the difference in years between each employee's start date, and the start of our annual leave calendar (Jan 1st 2022) to show me (a) how many years they have worked for us and then using a separate formula (b) how many days annual leave they are entitled to (it goes up after 5 years and then 10 years). All is working fine, except I want to add new staff due who are starting after Jan 1st this year, and want it to show as 0 for the number of years worked. Instead I get the dreaded #NUM message. (The weird thing is, on one of them it seems to be working fine!) 

See the screenshot below. Imagining that in this spreadsheet the rows started at 1 and the columns at A (so the one with the error message is C4) I am using the formula =DATEDIF(A4,B4,"Y")

Can anyone suggest anything (using simple language, I am new to this!) THANK YOU!!!

AL spreadsheet screenshot.png

4 Replies

@Neenalou 

=IFERROR(DATEDIF(A4,B4,"Y"),"start date greater end date")

or

=IFERROR(DATEDIF(A4,B4,"Y"),"")

 

Maybe with one of the above formulas, depending on the expected error response.

@Neenalou 

 

=DATEDIF(MIN(A1:B1),B1,"Y")

@Quadruple_Pawn Thank you! Appreciate it

Thanks for the speedy reply!