IF AND with multiple criteria's

rexhvn

New Member
Joined
Jan 7, 2016
Messages
27
Hi team,

Hope you're well.

Just after some assistance, please.

I'm trying to return a Text Value if a cell is between a date range, but I have mutliple date ranges that I want to return different values.

For Example:
If cell is less/equals or more/equal
I37 - 01/02/2024
M37 - 08/02/2024
Equals = 'Current"

I10 - 09/02/2024
M10 - 16/02/2024
Equals = "Next Week"

I've used the below IFS (AND formula, but it's not quite working.

=IFS(AND(AY2>='Cash Forecast'!$I$37,AY2<='Cash Forecast'!$M$37),"Current",AND(AY2>='Cash Forecast'!$I$10,AY2<='Cash Forecast'!$M$10),"Next Week",AND(AY2>='Cash Forecast'!$I$121,AY2<='Cash Forecast'!$M$121),"Week 1",AND(AY2>='Cash Forecast'!$I$148, "Week 2")

Any help is appreciated.

Kind Regards,
Dennis
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
I've used the below IFS (AND formula, but it's not quite working.
You haven't explained what you mean by "not quite working", but as the formula is posted, the syntax is incorrect. Presumably the formula should end:

Excel Formula:
=IFS(... ,AY2>='Cash Forecast'!$I$148, "Week 2")

You could simplify the formula by turning it round, e.g. if AY2 is not greater than or equal to 'Cash Forecast'!$I$148 (start of Week 2), then you don't need to test if it's less than or equal to 'Cash Forecast'!$M$121 (end of Week 1), etc.

Are your weeks really 8 days long, e.g. current week is 1 Feb to 8 Feb inclusive?

And do you need to extrapolate to "Week 3", "Week 4" etc? If so, I'd adopt a different formula based on counting the number of weeks forward from the "Current" week.
 
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,159
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top