IF AND with multiple criteria's

[rexhvn]

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

 

[StephenCrump]

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:

=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.