locked
Calculated date field that excludes weekends RRS feed

  • Question

  • Hi,

    I have a few calculated date variables that are all dependent on one date variable. It's set up like the one below:

    Proposal date = 05/30/2013

    Field date = Proposal date + 5 business days

    External meeting date = Proposal date + 12 days

    How can I set up Field date and External meeting date so that it excludes the weekends?

    A straight add will give me:

    Field date = 06/04/2013

    External meeting date = 06/11/2013

    What I need to see is:

    Field date = 06/06/2013

    External meeting date = 06/17/2013

    Can anyone help?

    Thursday, May 30, 2013 9:48 PM

Answers

  • Hi iseller138,

    We need to add validation on proposal date to be selected a date on weekend. Or if weekend is also allowed, then please modify the formula to following.

    Field date

    =IF(AND(WEEKDAY([proposal date]) >= 2, WEEKDAY([proposal date]) <= 6) ,[proposal date]+7, IF(WEEKDAY([proposal date]) = 7, [proposal date]+6, [proposal date]+5))

    And the External meeting date  column should be:

    =IF(AND(WEEKDAY([proposal date])>=1,WEEKDAY([proposal date])<=4),[proposal date]+16,IF(WEEKDAY([proposal date])=7,[proposal date]+17,[proposal date]+18))

    Thanks & Regards,
    Emir


    Emir Liu
    TechNet Community Support


    • Edited by Emir Liu Monday, June 3, 2013 8:14 AM
    • Marked as answer by Emir Liu Thursday, June 6, 2013 10:31 AM
    Monday, June 3, 2013 8:02 AM

All replies

  • Hi 

    I am not sure if this info will help you but I have a calculated field that tells me if the date is a week day or a weekend date. 

    =if(weekday([field with date in it])>5,"week end","week Day") Maybe using the weekday function may help you?

    Friday, May 31, 2013 8:31 AM
  • Hi jseller138,

    You may create the calculate column similar like this.

    Field date:
    =[proposal date]+7

    External meeting date:
    =IF(WEEKDAY([proposal date])>4,[proposal date]+18,[proposal date]+16)

    This only work if proposal date field only can be selected when it is on the work day. We need add validation to the proposal date to avoid weekend day is selected.

    Thanks & Regards,
    Emir


    Emir Liu
    TechNet Community Support

    Friday, May 31, 2013 10:12 AM
  • check this logic if this can help you:

    =IF(TEXT(WEEKDAY(Date_Time),"ddd")="sat","weekend","weekday")
    Friday, May 31, 2013 11:41 AM
  • var dateTime = DateTime.Now.AddBusinessDays(4);
    Friday, May 31, 2013 11:53 AM
  • How did you determine that adding 16 or 18 to the proposal date will give me the right date? If the date is a Sunday, adding 2 days would mean it will make the external meeting date a Tuesday rather than a Monday, is this correct?
    Sunday, June 2, 2013 8:59 PM
  • I set up a 'date and time' field and tried using the formula below to create a default calculated value:

    External Meeting Date was set up as a 'date and time' field.

    =IF(WEEKDAY([External Meeting Date]+5,3)>4,IF(WEEKDAY([External Meeting Date]+5,3)=5,[External Meeting Date]+7,[External Meeting Date]+6),[External Meeting Date]+5)

    I get an error saying "The formula contains reference(s) to field(s)."

    Can anyone help?


    Sunday, June 2, 2013 9:15 PM
  • Syntax is right in your formula. Are you sure that you are having a column with name "External Meeting Date" in your list.

    I hope you are trying to create a calculated column of type "Date & Time" and having the mentioned formula in the formula section.

    Try using "Created" out of box field to check whether the error is due to wrong column reference.

    =IF(WEEKDAY(Created+5,3)>4,IF(WEEKDAY(Created+5,3)=5,Created+7,Created+6),Created+5)


    Thanks



    Monday, June 3, 2013 6:50 AM
  • Hi iseller138,

    We need to add validation on proposal date to be selected a date on weekend. Or if weekend is also allowed, then please modify the formula to following.

    Field date

    =IF(AND(WEEKDAY([proposal date]) >= 2, WEEKDAY([proposal date]) <= 6) ,[proposal date]+7, IF(WEEKDAY([proposal date]) = 7, [proposal date]+6, [proposal date]+5))

    And the External meeting date  column should be:

    =IF(AND(WEEKDAY([proposal date])>=1,WEEKDAY([proposal date])<=4),[proposal date]+16,IF(WEEKDAY([proposal date])=7,[proposal date]+17,[proposal date]+18))

    Thanks & Regards,
    Emir


    Emir Liu
    TechNet Community Support


    • Edited by Emir Liu Monday, June 3, 2013 8:14 AM
    • Marked as answer by Emir Liu Thursday, June 6, 2013 10:31 AM
    Monday, June 3, 2013 8:02 AM