Posted on 2012-09-19
Last Modified: 2013-12-01

I need to determine the working days excluding Holidays and weekends to arrive at a completion Date.
I have a STartDate
Hours to Produce
Days to Produce based on 8hours and 5 Mins per day 5 Day Week
A Calendar in a separate table listing Public Holidays
I have used these custom functions to determine the Finish Date with Weekends and Holidays included.

The Problem is I am not getting acurate dates and it mat be the way I have used these ciustom finctions.
I have looked for a custom function that will give me a combination of the above two.In fact WeekDays offset looks right except it does not include Holidays.

I have attached an example..which is NOT working as I think I have used the custom functions incorrectly , however perhaps it will serve to clarify.
Question by:Krisraychris
    LVL 24

    Accepted Solution

    I believe the issue is that you are not using the WorkingDays custom function (CF) correctly. The function is looking for two dates and will give you the actual workdays in between by omitting from the count all weekend days and all holidays from the list.

    You have calculated the number of days each task will take  - hours / 8.09 (and are you rounding up or down). You then seem to be adding that number of days to the start date and then trying to determine the real end date (minus weekends and holidays) from there. What the function is going to give you is simply the number of working days between the start date and the date 2.1 days in the future, which in some cases could mean zero working days at all.

    What you are looking for then is not how many working there are between the start date and the start date plus X production days, but the DATE that the project will be finished, skipping over any weekends or holidays (I always find that stating a problem clearly leads most quickly to a solution).

    If you wanted to make this work, using a Custom Function, you could probably start with the WorkingDays function and modify it so that instead of starting with DateStart, DateEnd and Holidays to produce a result of a number of days, your variables would be DateStart, DaysAhead and Holidays and the result would be DateEnd.

    Author Comment

    Thank you for your reply.I agree this is exactly the problem.I am not sure how to go about modifying the function to get this result.Do you have any advice on how to go about it or do you know of a function I can use?
    LVL 24

    Expert Comment

    by:Will Loving
    Take a look at the function as it is and see if you can follow the logic of the calculation. If you can understand how the existing one works then you can probably figure out how to modify it to do what you want.

    Author Comment

    I have spent a lot of  time working through the logic of the custom function and there are some aspects of it I dont completeley  follow,

    I have modified to this but still needs refining and dont think it is correct yet,
    Let (
    $Date=$Date  + Mod ( DateStart - 1)     <5 IsEmpty ( FilterValues ( DateStart  ; HolidayList ) ) )+( Mod(WorkDays;5) ;
    WorkingDays ( DateStart + 1 ; WorkDays ; HolidayList )
     If there are no further comments on this then I will continue to modify.
    LVL 8

    Expert Comment

    This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Pop up windows can be a useful feature of any Filemaker database.  Though best used sparingly, they can be employed in a multitude of different ways, for example;  as a splash screen at login, during scripted processes to control user input, as pick…
    Problem: You have a hosted FileMaker database and users are tired of having to use Open Remote or Open Recent to access the database. They say, "can't you just give us something to double-click on rather than have to go through those dialogs?" An…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    This video discusses moving either the default database or any database to a new volume.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now