New Contributor

# SPOL Calculated Column Formula Help - Working Days

Hi,

I’m trying to find the solution to 2 calculated column requirements. I have searched quite extensively and have only found partial, or partially working answers.

The two requirements are:

1. Work out the number of working days between a query being received and acknowledged, and based on that number set a text line to “met” if 2 days or less or “not met” if over 2 days.
2. Based on the status of another column, working out the number of working days between either the date a query was received and closed, or if it is not closed, the number of working days between the date it was received and TODAY.

I am UK based and weekend days are Saturday and Sunday.

On 1, I’ve been playing with a formula like this, but it doesn’t seem to work consistently over weekends. Some seem to calculate correctly, and others don’t. So if a query is received Friday and closed Monday, it gives me 2 days on some (correct) and 4 on others. https://social.technet.microsoft.com/Forums/lync/en-US/2c450b0a-4e7b-41f5-88bf-42a43fb34d8b/networkd...

I haven’t found anything on 2.

My columns are:

Date received | Date acknowledged | Date closed | Status

In “Status” I have two closed status of “Closed” and “Closed Transferred” along with 3 classed as open which are “Open”, “Pending Info” and “Tracking Only”

Examples for 1

Date received - 1 March 2022

Date acknowledged - 2 March 2022

Output - “Met” (2 working days so SLA met)

Date received - 1 March 2022

Date acknowledged - 4 March 2022

Output - “Not Met” (4 working days so SLA not met)

Date received - 4 March 2022

Date acknowledged - 7 March 2022

Output - “Met” (2 working days as weekend days excluded so SLA met)

Examples for 2

Date received - 1 March 2022

Status - Closed

Date closed - 3 March 2022

Output - 3 Days (as status is closed, date closed - date received in working days)

Date received - 1 March 2022

Status - Pending Info

Date closed - BLANK

(Assume today is 4 March 2022)

Output - 4 Days (as status is pending info, TODAY - date received in working days)

Date received - 4 March 2022

Status - Closed

Date closed - 8 March 2022

Output - 3 Days (as status is closed, date closed - date received in working days and weekend days are excluded)

I hope the question is appropriate. I have all these working in excel using NETWORKDAYS but am no familiar with the syntax for SPOL. So I really would appreciate any help or information at all.

Thank you very much in advance for any help!