Multiple date range requests within a single formula

System

This discussion was created from comments split from: Return Value if Between Dates.

Rob Humbert

Hi,

Is it possible to increase the amount of date range requests within a single formula? I'm currently trying to return a value for first half of year and then 2nd half but for multiple years? It appears to be working for the 2021 year but not for the others.

Here is what I currently have: but receive unparseable error when the date is past 2022.

=IF(AND([Date of Survey]@row >= DATE(2021, 1, 1), [Date of Survey]@row <= DATE(2021, 7, 1)), "H1 2021", IF(AND([Date of Survey]@row >= DATE(2021, 7, 2), [Date of Survey]@row <= DATE(2021, 12, 31)), "H2 2021", IF(AND([Date of Survey]@row >= DATE(2022, 1, 1), [Date of Survey]@row <= DATE(2022, 7, 1), "H1 2022", IF(AND([Date of Survey]@row >= DATE(2022, 7, 2), [Date of Survey]@row <= DATE(2022, 12, 31), "H2 2022", IF(AND([Date of Survey]@row >= DATE(2023, 1, 1), [Date of Survey]@row <= DATE(2023, 7, 1), "H1 2023", IF(AND([Date of Survey]@row >= DATE(2023, 7, 2), [Date of Survey]@row <= DATE(2023, 12, 31), "H2 2023"))))))))))

Thank you for any assistance.

Genevieve P.

Hi @Rob Humbert

Yes this is possible! And your formula is almost there. The reason you're getting an error is because you don't close off any of the AND statement before listing the output for the IF.

Ex:

IF(AND([Date of Survey]@row >= DATE(2022, 1, 1), [Date of Survey]@row <= DATE(2022, 7, 1), "H1 2022",

Should be

IF(AND([Date of Survey]@row >= DATE(2022, 1, 1), [Date of Survey]@row <= DATE(2022, 7, 1)), "H1 2022",

So if we add in those parentheses in the right place (and remove them from the end of the formula), you get this:

=IF(AND([Date of Survey]@row >= DATE(2021, 1, 1), [Date of Survey]@row <= DATE(2021, 7, 1)), "H1 2021", IF(AND([Date of Survey]@row >= DATE(2021, 7, 2), [Date of Survey]@row <= DATE(2021, 12, 31)), "H2 2021", IF(AND([Date of Survey]@row >= DATE(2022, 1, 1), [Date of Survey]@row <= DATE(2022, 7, 1)), "H1 2022", IF(AND([Date of Survey]@row >= DATE(2022, 7, 2), [Date of Survey]@row <= DATE(2022, 12, 31)), "H2 2022", IF(AND([Date of Survey]@row >= DATE(2023, 1, 1), [Date of Survey]@row <= DATE(2023, 7, 1)), "H1 2023", IF(AND([Date of Survey]@row >= DATE(2023, 7, 2), [Date of Survey]@row <= DATE(2023, 12, 31)), "H2 2023"))))))

Let me know if this works for you!

Cheers,

Genevieve

Rob Humbert

Hi Genevieve,

Thank you so much. This fixed the issue and the information will really help in the future. Thank you again for all of your help. I hope you have a wonderful day!

Thank you,

Rob

Genevieve P.

Hi Rob,

No problem at all! I'm glad this worked for you.

Cheers,

Genevieve