Using "OR" statement with DATEDIF

kimkay

New Member
Joined
Sep 18, 2003
Messages
12
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

Well-known Member
Joined
Dec 30, 2008
Messages
6,910
Office Version
2019
Platform
Windows
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

Well-known Member
Joined
May 31, 2015
Messages
1,591
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

New Member
Joined
Sep 18, 2003
Messages
12
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.
 
Last edited:

kimkay

New Member
Joined
Sep 18, 2003
Messages
12
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

Well-known Member
Joined
May 31, 2015
Messages
1,591
Can you please tell me what result you are trying to get. The number of months between 2 dates, or years?
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
6,910
Office Version
2019
Platform
Windows
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.
 

Forum statistics

Threads
1,078,211
Messages
5,338,888
Members
399,265
Latest member
aj17x55

Some videos you may like

This Week's Hot Topics

Top