SharePoint List- Calculated column to return 2 different values across 5 date ranges based on Go Live Date

Question

Hi Team

I require a calculated column to return 2 different values across 5 date ranges based on when the Go Live Date is.

I've provided a table below of the date ranges and my expected results. If any task falls outside this date range, I want it to return a value off "No Date Concern"

Thanks Team,
Harry

Answer 1

Hi @Harry N Nomikos ,

Thanks for sharing the formula here.

By the way, since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others.". and according to the scenario introduced here: Answering your own questions on Microsoft Q&A, I would make a brief summary of this thread:

[SharePoint List- Calculated column to return 2 different values across 5 date ranges based on Go Live Date]

Issue Symptom:
I require a calculated column to return 2 different values across 5 date ranges based on when the Go Live Date is.

I've provided a table below of the date ranges and my expected results. If any task falls outside this date range, I want it to return a value off "No Date Concern"

Current status:
I've worked out the formula:

=IF(AND([Go Live Date]>=DATEVALUE("01/09/2023"),[Go Live Date]<=DATEVALUE("30/09/2023")),"Heightened Risk Period",IF(AND([Go Live Date]>=DATEVALUE("01/10/2023"),[Go Live Date]<=DATEVALUE("31/10/2023")),"Heightened Risk Period",IF(AND([Go Live Date]>=DATEVALUE("15/12/2023"),[Go Live Date]<=DATEVALUE("15/01/2024")),"Blackout Period",IF(AND([Go Live Date]>=DATEVALUE("01/02/2024"),[Go Live Date]<=DATEVALUE("29/02/2024")),"Heightened Risk Period",IF(AND([Go Live Date]>=DATEVALUE("01/06/2024"),[Go Live Date]<=DATEVALUE("30/06/2024")),"Blackout Period","No concern to Go Live Date")))))

You could click the "Accept Answer" button for this summary to close this thread, and this can make it easier for other community members to see the useful information when reading this thread. Thanks for your understanding!

Answer 2

=IF(AND([Go Live Date]>=DATEVALUE("01/09/2023"),[Go Live Date]<=DATEVALUE("30/09/2023")),"Heightened Risk Period",IF(AND([Go Live Date]>=DATEVALUE("01/10/2023"),[Go Live Date]<=DATEVALUE("31/10/2023")),"Heightened Risk Period",IF(AND([Go Live Date]>=DATEVALUE("15/12/2023"),[Go Live Date]<=DATEVALUE("15/01/2024")),"Blackout Period",IF(AND([Go Live Date]>=DATEVALUE("01/02/2024"),[Go Live Date]<=DATEVALUE("29/02/2024")),"Heightened Risk Period",IF(AND([Go Live Date]>=DATEVALUE("01/06/2024"),[Go Live Date]<=DATEVALUE("30/06/2024")),"Blackout Period","No concern to Go Live Date")))))