I'm creating a form whereby I want the date field to prepopulate to 3 business days from Today's date (exclude weekends). I'd also like to block the ability for users to select any days earlier than the 3 business days. In the calculated value field I used the formula =Today+3 which seems to work but it also includes weekends. How do tweak it to ensure it only calculates business days? In the column validation field I used =[Due date]>(TODAY()+3) which works but again I'd like it to only factor in business days. Can anyone help?
Add a comment
|
1 Answer
For the Calculated value for the column try this:
=IF(TEXT(WEEKDAY(TODAY()),"dddd")="Wednesday",TODAY()+5,IF(TEXT(WEEKDAY(TODAY()),"dddd")="Thursday",TODAY()+5,IF(TEXT(WEEKDAY(TODAY()),"dddd")="Friday",TODAY()+5,IF(TEXT(WEEKDAY(TODAY()),"dddd")="Saturday",TODAY()+4,TODAY()+3))))
For your column validation formula try this:
=AND([Due Date]>=TODAY()+3,NOT(TEXT(WEEKDAY([Due Date]),"dddd")="Saturday"),NOT(TEXT(WEEKDAY([Due Date]),"dddd")="Sunday"))
-
Thanks so much! Would you be able to modify the code to include wed, thur and fri? Meaning if today is wed then the date should prepopulate to next Mon (3 bus days). If today is thur then it should list next tues. And if it's fri it should display next Wed. All of these instances would push the date out to 3 business days. Today is fri but when i apply the code its still spitting out Mon as the due date.– JeromeApr 1 at 20:42
-
Yeah I had that all wrong sorry. I've updated the formula in my answer. Can you please try the new one? Apr 3 at 21:51
-