Forum Discussion

JonAust's avatar
JonAust
Copper Contributor
Jan 25, 2023
Solved

Formula help: How to determine if any dates within series of dates are within 6 months

Hi everyone, I am trying to create a formula to determine if any days that fall within a series of dates are within 6 months of a specific date but only dates that are earlier dates than the date in question.

 

Date series:

Date 1: Dec 3, 2020

Date 2: Sep 20, 2021

Date 3: Mar 5, 2021

Date 4: Jul 12, 2021

Date 5: May 4, 2022

 

If the date in question is Nov 25, 2021 the answer would be "yes" because Sep 20, 2021 and Jul 12, 2021 both fall within 6 months of Nov 25, 2021

 

But if the date in question is Apr 15, 2022 then the answer would be "no" because no earlier dates are within 6 months of Apr 15, 2022 (May 4, 2022 comes after Apr 15, 2022).

 

Does anyone know how to create a formula for this?

excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Jan 25, 2023

    JonAust 

    Let's say the series of dates is in B1:B5 and the specific date is in E1.

     

    =IF(COUNTIFS(B1:B5,">="&EDATE(E1,-6),B1:B5,"<"&E1),"Yes","No")

Resources