Using "OR" statement with DATEDIF

[kimkay]

Good morning! I need help with a DATEDIF formula. This is my original formula:

=ROUND(IF(YEAR(AC93)=2018 & MONTH(AC93)=8,"0",DATEDIF(AC93,"8/1/2018","m")/12),0)

It works perfectly when the date in cell AC93 is 8/1/2018, which is what most of the 1,000+ employees I'm calculating years of service for have as a start date. We have outliers who started on 1/1/2019, however, so I need a formula that will return "0" as the result when the date is 8/1/2019 OR 1/1/2019.

This is my current formula to try to return that "0" when cell AC93 contains either of the two dates, which is returning a #NAME ? error:

=ROUND(OR(IFS(YEAR(AC93)=2018 & MONTH(AC93)=8, YEAR(AC93)=2019 & MONTH(AC93))=1,"0",DATEDIF(AC93,"8/1/2018","m")/12),0)

Can someone help me to correct my formula? I'd appreciate it very much!!!!

Thanks,
Kim

 

[jasonb75]

The formula that you say works perfectly makes no sense, Employees that started on 8/1/18 have completed over 1 year of service based on the current date, so why 0?
If you want proper years of service then why not just use

=DATEDIF(start date, current date, "y")

 

[Jeffrey Mahoney]

Please try this
=ROUND(IF(AND(YEAR(AC93)=2018,MONTH(AC93)=8),0,DATEDIF(MIN(AC93,DATE(2018,8,1)),MAX(AC93,DATE(2018,8,1)),"M")/12),0)

 

[kimkay]

Hi Jason - I was testing on my data from last year; I should have explained that. I'll use 2019 when I apply this to my current year data.

 

[kimkay]

Hi Jeffrey! This works for my 1/1/2019 scenario and correctly returns a 0, but it doesn't work for dates prior to 8/1/2018 or 1/1/2019. For example, if the date in AC93 is 8/1/2016, the result should be 2. Your formula also returns 0 for that date.

 

[Jeffrey Mahoney]

I entered 08/01/2016 into AC93 and got a result of 2

 

[Jeffrey Mahoney]

Can you please tell me what result you are trying to get. The number of months between 2 dates, or years?

 

[jasonb75]

I'll use 2019 when I apply this to my current year data.

You shouldn't need to change the year if you use datedif properly, see post 2 or type datedif into google.