Weekday/Workday/Holidays Formula to calculate finish date

Hi everyone,

First post here so thank you in advance. I've used this forum so much in the past to find answers to questions but now I have one of my own that I have no idea how to overcome.

I have a sheet that is for planning jobs (attached is a ripped out very basic sample). Basically the start date in the top job begins with today's date, it has a certain number of hours allocated to it, which in turn is converted to days and then calculates a projected finish date. This then auto calculates the remaining jobs giving estimated start and finish dates as a rough plan.

This works fine using the below weekday formula. It correctly adds the number of days onto the date and even manages to know when to push it to the next day so you can't physically do more work than allows in an 8 hour day.

=IFERROR(IF(WEEKDAY(D2+C2,2)>5,D2+C2+(8-WEEKDAY(D2+C2,2)),D2+C2),"")

The only problem with this formula is that I have no way of including holidays/bank holidays etc. If I replace the non bracketed parts with WORKDAY(D2,C2,Holidays) then it includes holidays however it starts telling me I can do multiple jobs in one day that total more than 1 8 hour day so physically impossible!

I have attached a sample workbook.

If someone... anyone... can get the weekday formula working with holidays and pushing dates over to following days if cumulative value = more than an 8 hour day, I would be ever so grateful!

Cheers

Dan

Originally Posted by dflak

I think this might do it for you. I cooked this up about a decade ago. I also have NetWorkHours, too.

Please Login or Register  to view this content.

Thank you for this! How do i go about using it so i can give it a go? Functions are pretty new to me...

Ok... so I've managed to get it up and running but it just wont return the values I'm expecting.

Screenshot 2022-01-07 213159.png

As you can see in the example:

Job 1 & 2 are correct
I'd then expect Job 3 to start on the 7th but not be finished until the 17th as there has already been 6 out 8 hours work done that day and because job 3 is a 6 hour job, they can get 2 hours done on the 7th but cant be finished until the 17th due to the holiday days
The rest then appear to be correct on the face of it but hard to tell for certain.

Any ideas?