SharePoint List Calculated Column-Flag any Off Phone activity that occurs during a Heightened risk & Blackout period across 5 different Start Date & End Dates.

Question

Hi Team

I require a calculated column to flag any Off Phone activity that occurs during a Heightened risk & Blackout period across 5 different Start Date & End Dates.

Below is the Heightened Risk & Blackout Period date ranges

Below is my expected results with the column names that would need to be used.

If the "Readiness-Off the Tools" column returns;

"Off the Tools"- and falls within the above date ranges= "Off the tools during Blackout & Heightened Risk Period" (Refer to the Yellow coloured cell under the Expected Result column)

"Off the Tools"- and falls outside the above date ranges=Off the tools-outside the Blackout & Heightened Period or comms only (Refer to the Blue coloured cell under the Expected Result column)

"Comms Only" and falls within the above date ranges=Off the tools-outside the Blackout & Heightened Period or comms only (Refer to the Blue coloured cell under the Expected Result column)

"Comms Only" and falls outside the above date ranges=Off the tools-outside the Blackout & Heightened Period or comms only (Refer to the Blue coloured cell under the Expected Result column)

Regards,
Harry

Answer 1

Here is a formula for you:

=IF([Readiness-Off the Tools]="Off the Tools",IF(OR(OR(AND([End Date-(RA1)]>DATE(2023,9,1),[End Date-(RA1)]<DATE(2023,9,30)),AND([End Date-(RA1)]>DATE(2023,10,1),[End Date-(RA1)]<DATE(2023,10,31)),AND([End Date-(RA1)]>DATE(2023,12,15),[End Date-(RA1)]<DATE(2024,1,15)),AND([End Date-(RA1)]>DATE(2024,2,1),[End Date-(RA1)]<DATE(2024,2,29)),AND([End Date-(RA1)]>DATE(2024,6,1),[End Date-(RA1)]<DATE(2024,6,30))),OR(AND([End Date-(RA2)]>DATE(2023,9,1),[End Date-(RA2)]<DATE(2023,9,30)),AND([End Date-(RA2)]>DATE(2023,10,1),[End Date-(RA2)]<DATE(2023,10,31)),AND([End Date-(RA2)]>DATE(2023,12,15),[End Date-(RA2)]<DATE(2024,1,15)),AND([End Date-(RA2)]>DATE(2024,2,1),[End Date-(RA2)]<DATE(2024,2,29)),AND([End Date-(RA2)]>DATE(2024,6,1),[End Date-(RA2)]<DATE(2024,6,30))),OR(AND([End Date-(RA3)]>DATE(2023,9,1),[End Date-(RA3)]<DATE(2023,9,30)),AND([End Date-(RA3)]>DATE(2023,10,1),[End Date-(RA3)]<DATE(2023,10,31)),AND([End Date-(RA3)]>DATE(2023,12,15),[End Date-(RA3)]<DATE(2024,1,15)),AND([End Date-(RA3)]>DATE(2024,2,1),[End Date-(RA3)]<DATE(2024,2,29)),AND([End Date-(RA3)]>DATE(2024,6,1),[End Date-(RA3)]<DATE(2024,6,30))), OR(AND([End Date-(RA4)]>DATE(2023,9,1),[End Date-(RA4)]<DATE(2023,9,30)),AND([End Date-(RA4)]>DATE(2023,10,1),[End Date-(RA4)]<DATE(2023,10,31)),AND([End Date-(RA4)]>DATE(2023,12,15),[End Date-(RA4)]<DATE(2024,1,15)),AND([End Date-(RA4)]>DATE(2024,2,1),[End Date-(RA4)]<DATE(2024,2,29)),AND([End Date-(RA4)]>DATE(2024,6,1),[End Date-(RA4)]<DATE(2024,6,30))),OR(AND([End Date-(RA5)]>DATE(2023,9,1),[End Date-(RA5)]<DATE(2023,9,30)),AND([End Date-(RA5)]>DATE(2023,10,1),[End Date-(RA5)]<DATE(2023,10,31)),AND([End Date-(RA5)]>DATE(2023,12,15),[End Date-(RA5)]<DATE(2024,1,15)),AND([End Date-(RA5)]>DATE(2024,2,1),[End Date-(RA5)]<DATE(2024,2,29)),AND([End Date-(RA5)]>DATE(2024,6,1),[End Date-(RA5)]<DATE(2024,6,30)))),"Off the tools during Blackout & Heightened Risk Period","Off the tools-outside the Blackout & Heightened Period or comms only"),"Off the tools-outside the Blackout & Heightened Period or comms only")

Result:

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.