Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Question
Tuesday, June 3, 2014 2:29 AM
Hello,
I have a form which contains two fields: 'Start Date' and 'Due Date'. The 'Due Date' field is automatically set , it's always 4 more days than the 'Start Date' field.
The problem is: Those 4 days must be only business days... e.g., if 'Start Date' is on Friday then the 'Due Date' should be set on the next Thursday. Saturday and Sunday are ignored. Anyone has any idea of how to make this formula? I need to put it into a calculated SharePoint column.
Thanks in advance.
All replies (5)
Tuesday, June 3, 2014 10:53 PM âś…Answered
Ah, I did not think this through completely.
This formula will work correctly:
=IF(WEEKDAY(StartDate)<3,StartDate+4,IF(WEEKDAY(StartDate)<7,StartDate+6,StartDate+5))
If the start date is a Sunday or a Monday, we need to add 4 calendar days. For Tues through to Fri we need to add 6 calendar days to accommodate the weekend and on a Saturday, 5 calendar days will be added.
(the screenshot in my previous post is not a spreadsheet, by the way, it is the Datasheet View of the list.)
cheers, teylyn
Tuesday, June 3, 2014 4:33 AM | 1 vote
Did you check these posts?
http://excel.shilor.org/2010/07/sometimes-you-may-need-to-calculate.html
--Cheers
Tuesday, June 3, 2014 4:45 AM | 1 vote
Hello,
you can use this calculated formula for Due Date:
=IF(WEEKDAY(StartDate+4)=7,StartDate+6,IF(WEEKDAY(StartDate+4)=1,StartDate+5,StartDate+4))
Be aware that it treats holidays and national bank holidays as business days unless they are on a weekend.
cheers, teylyn
Tuesday, June 3, 2014 7:29 PM
Did you check these posts?
http://excel.shilor.org/2010/07/sometimes-you-may-need-to-calculate.html
--Cheers
![]()
Although I was looking for similar threads before opening mine, I could not find these two links. I will check them out, thank you.
Tuesday, June 3, 2014 7:41 PM
Hello,
you can use this calculated formula for Due Date:
=IF(WEEKDAY(StartDate+4)=7,StartDate+6,IF(WEEKDAY(StartDate+4)=1,StartDate+5,StartDate+4))
Be aware that it treats holidays and national bank holidays as business days unless they are on a weekend.
cheers, teylyn
teylyn,
Thank you for your example. However this formula doesn't seem to be entirely correct, right? Because according to the excel spreadsheet you sent, when the Start Date is May 29th (Thursday), Due Date will be on June 2nd (Monday) taking so only two days rather than four.
Maybe a little change in this formula and we will be fine.