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

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

Harry N Nomikos 1,336

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

User's image

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

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

Emily Du-MSFT 51,986 Reputation points Microsoft External Staff

2023-06-09T02:08:01.0266667+00:00

To provide the calculated column formula, I would like to confirm whether Start Date and End Date in the list or Start Date-(RA1), End Date-(RA1), Start Date-(RA2), End Date-(RA2) and etc. columns in the list?
Harry N Nomikos 1,336 Reputation points

2023-06-09T02:12:39.17+00:00

Hi @Emily Du-MSFT

It's the columns I listed in my snapshot above - Start Date-(RA1), End Date-(RA1),Start Date-(RA2), End Date-(RA2) etc

Thanks
Harry

1 answer

Your answer

Emily Du-MSFT 51,986 Reputation points Microsoft External Staff

2023-06-09T02:08:01.0266667+00:00

To provide the calculated column formula, I would like to confirm whether Start Date and End Date in the list or Start Date-(RA1), End Date-(RA1), Start Date-(RA2), End Date-(RA2) and etc. columns in the list?
Harry N Nomikos 1,336 Reputation points

2023-06-09T02:12:39.17+00:00

Hi @Emily Du-MSFT

It's the columns I listed in my snapshot above - Start Date-(RA1), End Date-(RA1),Start Date-(RA2), End Date-(RA2) etc

Thanks
Harry

Answer 1

Emily Du-MSFT 51,986 Microsoft External Staff

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:

User's image

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.

Harry N Nomikos 1,336 Reputation points

2023-06-09T06:51:45.06+00:00

Thanks @Emily Du-MSFT

I can see the date format you provided shows in YYYY/MM/DD format.

Will I have any issues inputting the above formula, despite my date format showing in DD/MM/YYYY?

Regards,
Harry
Emily Du-MSFT 51,986 Reputation points Microsoft External Staff

2023-06-09T06:58:26.19+00:00

If you use Data and time column to create Start Date-(RA1), End Date-(RA1), Start Date-(RA2), End Date-(RA2) and etc. columns, the formula will work well.
Emily Du-MSFT 51,986 Reputation points Microsoft External Staff

2023-06-12T02:17:52.77+00:00

I'm checking how the things are going on about this issue. Whether the answer helps you?

If there is any problem, please feel free to let me know.
Harry N Nomikos 1,336 Reputation points

2023-06-13T01:53:54.9266667+00:00

Hi @Emily Du-MSFT

These fields are already created as "Date Only" and I don't want it to have a time option because this field should only be a date option.

Could I please have the above formula amended to reflect "Date Only" and in "DD/MM/YYYY" format please ?

Cheers,
Harry
Emily Du-MSFT 51,986 Reputation points Microsoft External Staff

2023-06-13T03:05:21.49+00:00

I want to confirm the column type of Start Date-(RA1), End Date-(RA1), Start Date-(RA2), End Date-(RA2) and etc. columns. Are they date columns or text columns?
Harry N Nomikos 1,336 Reputation points

2023-06-13T03:08:44.63+00:00

Hi @Emily Du-MSFT - Start Date-(RA1), End Date-(RA1), Start Date-(RA2), End Date-(RA2) etc are date columns

Cheers,
Emily Du-MSFT 51,986 Reputation points Microsoft External Staff

2023-06-13T03:19:23.3766667+00:00

Per my test, the above formula works well in the "Date Only" and in "DD/MM/YYYY" format.

You could try it and if there is any problem, I can amend the formula.

Share via

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

1 answer

Your answer