Return value if date is between today and today+180

[RockFish74]

Hello,

I am struggling with a function that returns a quantity (value) if the expiry date falls between today and today+180 days. I would prefer not to use IF(AND)... if possible.

For example ... today's date is 2/6/2020 and 30 units expire on 6/14/2020 (within today+180) then 30 should be returned. If the 30 units expire on 9/12/2020 (falls outside of today + 180), then return a blank. Blanks should be returned if the date is less than today.

Any assistance is greatly appreciated!

 

[Joe4]

I am struggling with a function that returns a quantity (value) if the expiry date falls between today and today+180 days. I would prefer not to use IF(AND)... if possible.

Why? That is pretty much how you would do it!

If the date is in cell A2, then the formula might look something like:
=IF(A2>0,IF(AND(A2>=TODAY(),A2<=(TODAY()+180)),30,0),0)

 

[Snakehips]

It's not clear what sort of dataset you are trying to retrieve the number(s) from but maybe use something like below ?

Book1
	A	B	C	D
1	Expiring		Units	Expire
2	53		10	11/09/2020
3			20	12/09/2020
4		>	30	14/02/2020
5			40	13/09/2020
6			99	14/09/2020
7			60	15/09/2020
8			70	16/09/2020
9			80	17/09/2020
10			90	18/09/2020
11			100	19/09/2020
12			22	20/09/2020
13			14	21/09/2020
14			130	22/09/2020
15			140	23/09/2020
16		>	23	14/02/2020
17			66	25/09/2020
18			170	26/09/2020
19			180	27/09/2020
20			190	28/09/2020
Sheet5

Cell Formulas
Range	Formula
A2	A2	=SUMPRODUCT(C2:C20*(D2:D20<=(TODAY()+180)))

Hope that helps.

 

[RockFish74]

Why? That is pretty much how you would do it!

If the date is in cell A2, then the formula might look something like:
=IF(A2>0,IF(AND(A2>=TODAY(),A2<=(TODAY()+180)),30,0),0)

Thank you! Your formula worked!

 

[RockFish74]

It's not clear what sort of dataset you are trying to retrieve the number(s) from but maybe use something like below ?

Book1
	A	B	C	D
1	Expiring		Units	Expire
2	53		10	11/09/2020
3			20	12/09/2020
4		>	30	14/02/2020
5			40	13/09/2020
6			99	14/09/2020
7			60	15/09/2020
8			70	16/09/2020
9			80	17/09/2020
10			90	18/09/2020
11			100	19/09/2020
12			22	20/09/2020
13			14	21/09/2020
14			130	22/09/2020
15			140	23/09/2020
16		>	23	14/02/2020
17			66	25/09/2020
18			170	26/09/2020
19			180	27/09/2020
20			190	28/09/2020
Sheet5

Cell Formulas
Range	Formula
A2	A2	=SUMPRODUCT(C2:C20*(D2:D20<=(TODAY()+180)))

Hope that helps.

Thank you for the help!

 

[Joe4]

You are welcome.