1

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?

1 Answer 1

0

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"))
3
  • 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.
    – Jerome
    Apr 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?
    – user79408
    Apr 3 at 21:51
  • Brilliant! Thank you so much for your help with this!
    – Jerome
    Apr 4 at 14:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.