Formula to see if a date is before, during or after a specific date range

[sammy2xx]

I am trying to see if a date is before, during or after a specific date range.

For example:

A1 16/01/2020

A2 01/01/19 B2 31/12/19
A3 01/01/20 B3 31/12/20
A4 01/01/21 B3 31/12/21

I need a formula for C2,C3 and C4 to see if the date in A1 is before, during or after each range.

So in this instance C2 = Before, C3 = During, C4 = After

 

[Eric W]

Welcome to the forum!

Try:

Book1
	A	B	C
1	1/16/2020
2	1/1/2019	12/31/2019	After
3	1/1/2020	12/31/2020	During
4	1/1/2021	12/31/2021	Before
Sheet28

Cell Formulas
Range	Formula
C2:C4	C2	=IF(A$1<A2,"Before",IF(A$1>B2,"After","During"))

I think you might have reversed the "Before" and "After" in your example, since 1/16/2020 (January 16, 2020) is after the first range. If you want it the other way, swap the "After" and "Before" values in the formula.

 

[Norie]

Try this in C2 and copy down.

=IF(AND($A$1>A2, $A$1<B2),"During",IF($A$1>B2,"After","Before"))

 

[Fluff]

If your date ranges are always whole years, another option

+Fluff New.xlsm
	A	B	C
1	16/01/2020
2	01/01/2019	31/12/2019	After
3	01/01/2020	31/12/2020	During
4	01/01/2021	31/12/2021	Before
List

Cell Formulas
Range	Formula
C2:C4	C2	=CHOOSE(SIGN(YEAR(A$1)-YEAR(A2))+2,"Before","During","After")

 

[sammy2xx]

Thank you kindly! Couldn't see the wood for the trees.

 

[Fluff]

Glad we could help & thanks for the feedback.