SharePoint list Calculate a date from an existing date field for Working/Business days
HTML-код
- Опубликовано: 19 фев 2022
- SharePoint list - Add column automatically calculating date from an existing date field - For Working Days/Business Days - This formula ignores weekends and only works on Business days.
👀 Link to Weekday documentation:
👉 support.microsoft.com/en-us/o...
The formula used for calculating 2 business days in advance:
=IF(WEEKDAY([Created Date 1])=1,[Created Date 1]+2,
IF(WEEKDAY([Created Date 1])=2,[Created Date 1]+2,
IF(WEEKDAY([Created Date 1])=3,[Created Date 1]+2,
IF(WEEKDAY([Created Date 1])=4,[Created Date 1]+2,
IF(WEEKDAY([Created Date 1])=5,[Created Date 1]+4,
IF(WEEKDAY([Created Date 1])=6,[Created Date 1]+4,
IF(WEEKDAY([Created Date 1])=7,[Created Date 1]+3)))))))
👉 Hey welcome to WeTechCareOfYou!
If you are a returning subscriber we wish to say thankyou!
If you are new to the channel, please subscribe as we release a new video every week. If you have any questions around our videos please just leave a comment and we will get back to you. If you would like to discuss a project or require set hours for development, support or maintenance then please email Admin@WeTechCareOfYou.com or complete a form here: wetechcareofyou.com/contact/
We're also happy to introduce you to a selection of products that have been instrumental in transforming and optimizing our business. As a valued visitor/member of our network, we believe these tools could greatly benefit you too. We've joined the affiliate program to provide you with access and potential discounts:
✅Express VPN - Get a 30-Day Money Back Guarantee with our link.
A VPN (virtual private network) is the easiest and most effective way for people to protect their internet traffic and keep their identities private online. When Express VPN is switched on, your internet traffic goes through an encrypted tunnel that nobody can see into, including hackers, governments, and your internet service provider:
👉 click.linksynergy.com/fs-bin/...
✅Microsoft 365 Business Basic - This license is perfect if you are starting from scratch, with low costs you can have Teams, OneDrive, SharePoint and Exchange to fully operate with communication, collaboration and storage:
👉 click.linksynergy.com/fs-bin/...
✅Microsoft 365 Business Standard - This license is the next step up from Business Basic and gives access to a lot more Microsoft 365 apps including the Microsoft suite of Word, Excel and PowerPoint:
👉 click.linksynergy.com/fs-bin/...
*Please note the above links provided are affiliate links, which means we may earn a commission from any purchases. We have personally selected these products based on their value to WeTechCareOfYou, and we believe they can benefit you as well.
Kind Regards,
The WeTechCareOfYou Team 💙
Related Tags:-
SharePoint list Calculate,SharePoint list,SharePoint,date from an existing date field for Working/Business days,date field for Working/Business days,sharepoint online,calculated column,sharepoint,microsoft sharepoint,date field,SharePoint list Calculate a date from an existing date field,date field for working/business days,power bi,date from an existing date field for working/business days,calculated column formula,date from an existing date field,power bi tutorial 
Наука
![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!
Good Formula ty for giving me the pieces I needed
Hey thanks for the great comment Lewinsai, happy it helped!
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...
Hey Robin,
Please try this:
=IF([Residual Risk Score]>15,[Date Last Reviewed]+90)
Let me know if it works.
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'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?
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!
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! 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, 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)))))))
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)))))))