locked
Leave Request List Calculation RRS feed

  • Question

  • I've created a leave request list.  Calculated columns:

    Day of first day

    Day of last day

    Days applied for 

    Need it to calculate total number of days applied for, excluding weekends.  

    =(DATEDIF([Date of first day];[Date of last day];"D"))-INT(DATEDIF([[Date of first day];
    [Date of last day];"D")/7*2)-IF((WEEKDAY([Date of last day])-WEEKDAY([Date of first day]))
    <0;2;0)-IF((WEEKDAY([Date of last day])-WEEKDAY([Date of first day]))=5;1;0)+1

    After adding this formula to field "Days applied for", I get this error:  The formula refers to a column that does not exist.  Check the formula for spelling mistakes or change the non-existing column to an existing column.  Am I missing something?

    Friday, June 10, 2016 2:01 PM

Answers

All replies

  • Hi

    the formula is more complicated

    Please check my post , for working hours, but use only days number

    http://www.romeodonca.ro/Lists/Posts/Post.aspx?List=3fd2dd39-fa51-4aee-a0fc-154d9cc92d36&ID=175&ContentTypeId=0x0110009F1363098627144DB6C0FBD7FAA40365

    and let me know if you need more details


    Romeo Donca, Orange Romania (MCSE, MCITP, CCNA) Please Mark As Answer if my post solves your problem or Vote As Helpful if the post has been helpful for you.


    • Proposed as answer by romeo donca Friday, June 10, 2016 2:13 PM
    • Unproposed as answer by romeo donca Friday, June 10, 2016 2:13 PM
    • Edited by romeo donca Friday, June 10, 2016 2:13 PM
    • Proposed as answer by croute1 Friday, June 10, 2016 3:37 PM
    Friday, June 10, 2016 2:13 PM
  • Hi Chantelle,

    From your description, you have a leave request list which contains columns:

    Day of first day

    Day of last day

    For your issue that "The formula refers to a column that does not exist".

    Change the following:

    [Date of first day] to [Day of first day]

    [Date of last day] to [Day of last day]

    Besides, here is a similar post about calculating days between two dates excluding weekends:

    https://social.msdn.microsoft.com/Forums/sharepoint/en-US/d6a6b014-c09c-4436-9f18-0f151bae85b5/caculated-column-formula-for-workdays-between-two-dates-excluding-weekends-datediff?forum=sharepointcustomizationlegacy

    Note: change the columns to your columns.

    Best Regards,

    Lisa Chen


    TechNet Community Support
    Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    Monday, June 13, 2016 9:33 AM
  • Hi Chantelle,

    Is there anything update?

    Please remember to mark the reply as an answer if it helps.

    Have a nice day!

    Best Regards,

    Lisa Chen


    TechNet Community Support
    Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    Tuesday, June 21, 2016 1:42 AM