Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 251
  • Last Modified:



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.
  • 2
  • 2
1 Solution
Will LovingPresidentCommented:
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.
KrisraychrisAuthor Commented:
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?
Will LovingPresidentCommented:
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.
KrisraychrisAuthor Commented:
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.
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now