Hi, I have a situation where I have 2columns in the Sharepoint list, Task Start Date and SLA days (its different for different tasks). I have to get values in another column named Due date which will give a date adding Task Start Date and SLA days considering the weekends...Can you please suggest a formula where SLA days are variable for each item in the list.
Hey Bonikona, I have built somthing similar in the past. Please create a new calculated column in the list, and in the formula use an expression similar to the following: =DATE(YEAR([Task Start Date]),MONTH([Task Start Date]),DAY([Last Completed Date]+[SLA Days])) Set the column as date and time value.
@@WeTechCareOfYou thanks for your reply. I don't have any column named "Last Completed Date"......I believe it will be Task Start Date, I tried this formula, but its not considering the weekends :(
Hi can you suggest any formula to calculate a date: Say for example I have a DateSubmitted=08/02/2023 and I need a date after 60 business days excluding the weekends how would I implement that?
Hey meenak, Please try the following: =IF(WEEKDAY([Created Date 1])=1,[Created Date 1]+82, IF(WEEKDAY([Created Date 1])=2,[Created Date 1]+81, IF(WEEKDAY([Created Date 1])=3,[Created Date 1]+83, IF(WEEKDAY([Created Date 1])=4,[Created Date 1]+83, IF(WEEKDAY([Created Date 1])=5,[Created Date 1]+83, IF(WEEKDAY([Created Date 1])=6,[Created Date 1]+84, IF(WEEKDAY([Created Date 1])=7,[Created Date 1]+83)))))))
Great video and almost does exactly what I want to happen in my SharePoint list. I have created a list where I need to calculate the date based on the value of 1 column and the date in another column to provide me a date either 3 months, 6 months or 12 months later depending on the value in column 1. So far I have this formula but keep getting an error. =IF([Residual Risk Score]">15",THEN[Date Last Reviewed],"+90days"). Where am I going wrong.. Complete newbie to formulas. Many thanks in advance for any advice...
Hi, thanks for, this. i need this exact solution but for an existing list - SLA is 5 days, not inc bank holidays. Do need to create a new list? can by existing list reference another list? thank you James
Hey James, you will need to add the column to the existing list which you would like to calculate the date from. Or is your request that you NEED to calculate the dates in a different list? Also my commands only work for weekends and do not include public holidays as this would require a lot more development to detect which country/state etc.
@@WeTechCareOfYou I've got a request list form and need to add an SLA Date 5 days from created but exclude weekends, not fussed about bank holidays for now. if you could assist that would be really helpful. please let me know what column to add to this existing form list. and code for 5 days. Thank you, much appreciated :-) James
Ok if you just add the calculated column to the request list and use the following formula it should calculate 5 business days for your SLA date: =IF(WEEKDAY([Created Date 1])=1,[Created Date 1]+5, IF(WEEKDAY([Created Date 1])=2,[Created Date 1]+7, IF(WEEKDAY([Created Date 1])=3,[Created Date 1]+7, IF(WEEKDAY([Created Date 1])=4,[Created Date 1]+7, IF(WEEKDAY([Created Date 1])=5,[Created Date 1]+7, IF(WEEKDAY([Created Date 1])=6,[Created Date 1]+7, IF(WEEKDAY([Created Date 1])=7,[Created Date 1]+6)))))))
ah ofcourse im sorry. So if you want to calculate it from when your list item is created, we can use the out of the box created date which is automatically generated when you add the record. Please try the following and see if this works: =IF(WEEKDAY([Created])=1,[Created]+5, IF(WEEKDAY([Created])=2,[Created]+7, IF(WEEKDAY([Created])=3,[Created]+7, IF(WEEKDAY([Created])=4,[Created]+7, IF(WEEKDAY([Created])=5,[Created]+7, IF(WEEKDAY([Created])=6,[Created]+7, IF(WEEKDAY([Created])=7,[Created]+6)))))))
I'm trying to do something simliar, but I want the formula to pick a #workdays to add to a date from my SharePoint list (i.e. task due date - effort duration = task start date). I would use workday in excel to do this but can't figure it out for SharePoint lists.
Hey Caitlin, apologies for the late reply. I am now available to try and create this calculation for you, if you can please confirm you still require this or did you get it resolved?
Hi, great video. I have trouble to exclude weekends when i calculate total days for leave application. right now i use =TEXT([Start Date] - [End Date], "D"), can you show me other formula so that it will exclude weekends in the calculation?
I have a condition that I need to verify before I calculate a [Term Deadline] date. If there are [term Days] then I need to take the [expiration date] and minus the [term days] to determine the [Term Deadline]. If there are zero [term days] I dont want to do anything! My formula needs to add this condition. Can you help? =[Expiration Date]-[Term Days] If the [Term Days] is zero then i dont want to calculate a [Term Deadline]
Great video! Is it also possible to factor in a choice column where certain choices/requests have different SLA dates. For example, request A = 5 working days SLA whereas request B = 3 working days SLA.
Hey Adam, thankyou for the comment and sorry for the late response, I got married 3 days ago and have been very busy. We can achieve what you are asking, please try the following: =IF(SLA="A",Created+2, IF(SLA="B",Created+7))
Hey thankyou for the comment J Geronimo! If you wish to calculate the working date fields from an existing date you can simply add the name of that date field instead of "Created Date 1" in my video at 4:12
@@WeTechCareOfYou Thank you for your reply. I was actually referring to the week number. For example, we're currently in work week 19. Next week will be work week 20. Any thoughts?
ah ok I understand. I just had a quick look online and found this blog which looks to be what you need? www.gopenly.in/spo-how-to-calculate-week-number-from-date-column/
@@WeTechCareOfYou thank you. Unfortunately that formula didn't work, but after some exploring I found this formula worked perfectly: =INT(([Due date]-DATE(YEAR([Due date]),1,1)+(TEXT(WEEKDAY(DATE(YEAR([Due date]),1,1)),"d")))/7)+1 Just replace the "Due date" with your date column.
HI i think I am close! I have a list where the person entering a new item will enter the day their maintenance will start. This is a time/date column called 'Maintenance Starts.' I also have a calculated column based on 'Maintenance Starts.' I want this calculated column to produce a time date that is 5 BUSINESS Days sooner than the 'Maintenance Starts.' I have worked up the following formula based on your AWESOME video and it keeps erroring out. =IF(WEEKDAY([Maintenance Starts])=1, [Maintenance Starts]-6, =IF(WEEKDAY([Maintenance Starts])=2, [Maintenance Starts]-7, =IF(WEEKDAY([Maintenance Starts])=3, [Maintenance Starts]-7, =IF(WEEKDAY([Maintenance Starts])=4, [Maintenance Starts]-5, =IF(WEEKDAY([Maintenance Starts])=5, [Maintenance Starts]-5, =IF(WEEKDAY([Maintenance Starts])=6, [Maintenance Starts]-5, =IF(WEEKDAY([Maintenance Starts])=7, [Maintenance Starts]-5))))))) The prior formula that worked but didn't take into account weekends. =[Maintenance Starts]-5 thanks in advance!
Hey Ashley, thankyou for the comment. I can see you dont need to put all of the "=" in. Please try it again without them: =IF(WEEKDAY([Maintenance Starts])=1, [Maintenance Starts]-6, IF(WEEKDAY([Maintenance Starts])=2, [Maintenance Starts]-7, IF(WEEKDAY([Maintenance Starts])=3, [Maintenance Starts]-7, IF(WEEKDAY([Maintenance Starts])=4, [Maintenance Starts]-5, IF(WEEKDAY([Maintenance Starts])=5, [Maintenance Starts]-5, IF(WEEKDAY([Maintenance Starts])=6, [Maintenance Starts]-5, IF(WEEKDAY([Maintenance Starts])=7, [Maintenance Starts]-5)))))))
![Trick - Microsoft List Calculated vs Formula Date Fields [2622.748]](/img/1.gif)
Excellent explanation, to the point.I will try
Hey Sheatg, thankyou for the great comment, happy the video helped!
Hi, I have a situation where I have 2columns in the Sharepoint list, Task Start Date and SLA days (its different for different tasks). I have to get values in another column named Due date which will give a date adding Task Start Date and SLA days considering the weekends...Can you please suggest a formula where SLA days are variable for each item in the list.
Hey Bonikona, I have built somthing similar in the past. Please create a new calculated column in the list, and in the formula use an expression similar to the following:
=DATE(YEAR([Task Start Date]),MONTH([Task Start Date]),DAY([Last Completed Date]+[SLA Days]))
Set the column as date and time value.
@@WeTechCareOfYou thanks for your reply. I don't have any column named "Last Completed Date"......I believe it will be Task Start Date,
I tried this formula, but its not considering the weekends :(
Hi can you suggest any formula to calculate a date: Say for example I have a DateSubmitted=08/02/2023 and I need a date after 60 business days excluding the weekends how would I implement that?
Hey meenak,
Please try the following:
=IF(WEEKDAY([Created Date 1])=1,[Created Date 1]+82,
IF(WEEKDAY([Created Date 1])=2,[Created Date 1]+81,
IF(WEEKDAY([Created Date 1])=3,[Created Date 1]+83,
IF(WEEKDAY([Created Date 1])=4,[Created Date 1]+83,
IF(WEEKDAY([Created Date 1])=5,[Created Date 1]+83,
IF(WEEKDAY([Created Date 1])=6,[Created Date 1]+84,
IF(WEEKDAY([Created Date 1])=7,[Created Date 1]+83)))))))
Good Formula ty for giving me the pieces I needed
Hey thanks for the great comment Lewinsai, happy it helped!
Great video and almost does exactly what I want to happen in my SharePoint list. I have created a list where I need to calculate the date based on the value of 1 column and the date in another column to provide me a date either 3 months, 6 months or 12 months later depending on the value in column 1. So far I have this formula but keep getting an error. =IF([Residual Risk Score]">15",THEN[Date Last Reviewed],"+90days"). Where am I going wrong.. Complete newbie to formulas. Many thanks in advance for any advice...
Hey Robin,
Please try this:
=IF([Residual Risk Score]>15,[Date Last Reviewed]+90)
Let me know if it works.
Hi, thanks for, this. i need this exact solution but for an existing list - SLA is 5 days, not inc bank holidays.
Do need to create a new list? can by existing list reference another list? thank you
James
Hey James, you will need to add the column to the existing list which you would like to calculate the date from. Or is your request that you NEED to calculate the dates in a different list?
Also my commands only work for weekends and do not include public holidays as this would require a lot more development to detect which country/state etc.
@@WeTechCareOfYou I've got a request list form and need to add an SLA Date 5 days from created but exclude weekends, not fussed about bank holidays for now.
if you could assist that would be really helpful. please let me know what column to add to this existing form list. and code for 5 days. Thank you, much appreciated :-)
James
Ok if you just add the calculated column to the request list and use the following formula it should calculate 5 business days for your SLA date:
=IF(WEEKDAY([Created Date 1])=1,[Created Date 1]+5,
IF(WEEKDAY([Created Date 1])=2,[Created Date 1]+7,
IF(WEEKDAY([Created Date 1])=3,[Created Date 1]+7,
IF(WEEKDAY([Created Date 1])=4,[Created Date 1]+7,
IF(WEEKDAY([Created Date 1])=5,[Created Date 1]+7,
IF(WEEKDAY([Created Date 1])=6,[Created Date 1]+7,
IF(WEEKDAY([Created Date 1])=7,[Created Date 1]+6)))))))
@@WeTechCareOfYou sorry if i'm being silly but i dont have the Created Date 1 column. i don't understand.. :-(
ah ofcourse im sorry. So if you want to calculate it from when your list item is created, we can use the out of the box created date which is automatically generated when you add the record. Please try the following and see if this works:
=IF(WEEKDAY([Created])=1,[Created]+5,
IF(WEEKDAY([Created])=2,[Created]+7,
IF(WEEKDAY([Created])=3,[Created]+7,
IF(WEEKDAY([Created])=4,[Created]+7,
IF(WEEKDAY([Created])=5,[Created]+7,
IF(WEEKDAY([Created])=6,[Created]+7,
IF(WEEKDAY([Created])=7,[Created]+6)))))))
I'm trying to do something simliar, but I want the formula to pick a #workdays to add to a date from my SharePoint list (i.e. task due date - effort duration = task start date). I would use workday in excel to do this but can't figure it out for SharePoint lists.
Hey Caitlin, apologies for the late reply. I am now available to try and create this calculation for you, if you can please confirm you still require this or did you get it resolved?
Hi, great video. I have trouble to exclude weekends when i calculate total days for leave application. right now i use =TEXT([Start Date] - [End Date], "D"), can you show me other formula so that it will exclude weekends in the calculation?
Hey Norman, apologies for the late response. Do you still need help with this issue?
I have a condition that I need to verify before I calculate a [Term Deadline] date. If there are [term Days] then I need to take the [expiration date] and minus the [term days] to determine the [Term Deadline]. If there are zero [term days] I dont want to do anything! My formula needs to add this condition. Can you help? =[Expiration Date]-[Term Days] If the [Term Days] is zero then i dont want to calculate a [Term Deadline]
Hey shoz, please try the following:
=IF([Term Days] > 0,
[Expiration Date]-[Term Days],
"")
Great video! Is it also possible to factor in a choice column where certain choices/requests have different SLA dates. For example, request A = 5 working days SLA whereas request B = 3 working days SLA.
Hey Adam, thankyou for the comment and sorry for the late response, I got married 3 days ago and have been very busy.
We can achieve what you are asking, please try the following:
=IF(SLA="A",Created+2,
IF(SLA="B",Created+7))
@@WeTechCareOfYou congrats on the wedding! Thanks for your response, I’ll give that a go.
@@adammulligan6543 Thankyou!
Great video! Question: How do you generate the work week from an existing date field?
Hey thankyou for the comment J Geronimo! If you wish to calculate the working date fields from an existing date you can simply add the name of that date field instead of "Created Date 1" in my video at 4:12
@@WeTechCareOfYou Thank you for your reply. I was actually referring to the week number. For example, we're currently in work week 19. Next week will be work week 20. Any thoughts?
ah ok I understand. I just had a quick look online and found this blog which looks to be what you need?
www.gopenly.in/spo-how-to-calculate-week-number-from-date-column/
@@WeTechCareOfYou thank you. Unfortunately that formula didn't work, but after some exploring I found this formula worked perfectly: =INT(([Due date]-DATE(YEAR([Due date]),1,1)+(TEXT(WEEKDAY(DATE(YEAR([Due date]),1,1)),"d")))/7)+1
Just replace the "Due date" with your date column.
ah perfect! thankyou for providing the working formula Geronimo and its good to hear you got it working!
HI i think I am close! I have a list where the person entering a new item will enter the day their maintenance will start. This is a time/date column called 'Maintenance Starts.'
I also have a calculated column based on 'Maintenance Starts.' I want this calculated column to produce a time date that is 5 BUSINESS Days sooner than the 'Maintenance Starts.'
I have worked up the following formula based on your AWESOME video and it keeps erroring out.
=IF(WEEKDAY([Maintenance Starts])=1, [Maintenance Starts]-6,
=IF(WEEKDAY([Maintenance Starts])=2, [Maintenance Starts]-7,
=IF(WEEKDAY([Maintenance Starts])=3, [Maintenance Starts]-7,
=IF(WEEKDAY([Maintenance Starts])=4, [Maintenance Starts]-5,
=IF(WEEKDAY([Maintenance Starts])=5, [Maintenance Starts]-5,
=IF(WEEKDAY([Maintenance Starts])=6, [Maintenance Starts]-5,
=IF(WEEKDAY([Maintenance Starts])=7, [Maintenance Starts]-5)))))))
The prior formula that worked but didn't take into account weekends. =[Maintenance Starts]-5
thanks in advance!
Hey Ashley, thankyou for the comment. I can see you dont need to put all of the "=" in. Please try it again without them:
=IF(WEEKDAY([Maintenance Starts])=1, [Maintenance Starts]-6,
IF(WEEKDAY([Maintenance Starts])=2, [Maintenance Starts]-7,
IF(WEEKDAY([Maintenance Starts])=3, [Maintenance Starts]-7,
IF(WEEKDAY([Maintenance Starts])=4, [Maintenance Starts]-5,
IF(WEEKDAY([Maintenance Starts])=5, [Maintenance Starts]-5,
IF(WEEKDAY([Maintenance Starts])=6, [Maintenance Starts]-5,
IF(WEEKDAY([Maintenance Starts])=7, [Maintenance Starts]-5)))))))