SOLVED

# Datedif function for duplicates

Copper Contributor

# Datedif function for duplicates

Hi I'm trying an excel formula for the below. I need to find the difference of the highlighted dates for each ID in months. Thank you

 ID Start Date End Date 1 5/1/2022 1/31/2023 1 6/1/2022 1/31/2023 2 6/1/2022 2/28/2023 2 9/1/2022 2/28/2023
6 Replies

# Re: Datedif function for duplicates

In D2:

=C2-MAXIFS(\$B\$2:\$B\$100, \$A\$2:\$A\$100, A2)

Adjust the ranges if you have more than 100 rows of data.

# Re: Datedif function for duplicates

@Hans Vogelaar Thank you - Is it possible to get the difference of the dates in months? right now it gives me a 0. For eg for ID 1 I want cell D2 to show 6

# Re: Datedif function for duplicates

In months:

=DATEDIF(MAXIFS(\$B\$2:\$B\$100, \$A\$2:\$A\$100, A2), C2, "m")

# Re: Datedif function for duplicates

Thank you. This is now giving me a #Name error. Is there any issue with syntax? I understand datedif() takes in a start date, end date as arguments
best response confirmed by smanas87 (Copper Contributor)
Solution

# Re: Datedif function for duplicates

Which version of Excel do you use? MAXIFS is available in Office 2019, Office 2021 and Microsoft 365.

If you have an older version, try the following formula, confirmed by pressing Ctrl+Shift+Enter:

=DATEDIF(MAX(IF(\$A\$2:\$A\$100=A2, \$B\$2:\$B\$100)), C2, "m")

# Re: Datedif function for duplicates

Thank you - I'm using the 2016 version let me try the latest one.
1 best response

Accepted Solutions
best response confirmed by smanas87 (Copper Contributor)
Solution

# Re: Datedif function for duplicates

Which version of Excel do you use? MAXIFS is available in Office 2019, Office 2021 and Microsoft 365.

If you have an older version, try the following formula, confirmed by pressing Ctrl+Shift+Enter:

=DATEDIF(MAX(IF(\$A\$2:\$A\$100=A2, \$B\$2:\$B\$100)), C2, "m")