Forum Discussion
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:
- 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.
- 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/networkdays-in-sharepoint-calculated-column?forum=sharepointgenerallegacy
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!
- Have just been informed TODAY won’t work when viewing the list, as it only executes when a list entry is updated.
