How to use WORKDAY to calculate the Final Training Day and exclude Weekends and Public Holidays

[ExcelNoobUser]

Hi, I wanted to ask if there was a better for me to calculate when someone would complete their training in the snippet below. I'm trying to calculate 6 people who are required to attend 300 hours of training everyday excluding weekends and public holidays, with each person having a different amount of hours of training depending on which package they are assigned to. I initially tried to divide the 300 hours into workdays by dividing 300 by the hours in the package with the formula in B4 being 300/F4 and IF for F4 being A to divide by 8, B divide by 7 and C divide by 6 and so on for D, E and F. I got 50 for the amount of days in B4 but this did not include the weekends and did not include the public holidays that I listed in Column M. I then tried to use the WORKDAY function with the start date for B4 being G4, then taking amount of days which I initially wanted to manually add by multiplying it by 1.4 to get 7/5 days in a week and then add the holiday with M4:M9. I tried to do this in one cell in B4 but got #NAME! during my initial attempts. I then tried to separate both functions by calculating the amount of days in training in C4 and multiplying it by 1.4 before referencing it again in B4 for the amount of days but still got the #NAME! error. Is there anything I can do to solve this, with either using one cell for all the formulas to using multiple cells to reference with the WORKDAY function or any other function that can allow me to get the Stop Date without error.

 

[Fluff]

How about

=WORKDAY(G4,300/XLOOKUP(F4,$J$4:$J$9,$K$4:$K$9),$M$4:$M$100)

 

[ExcelNoobUser]

How about

=WORKDAY(G4,300/XLOOKUP(F4,$J$4:$J$9,$K$4:$K$9),$M$4:$M$100)

Wait this actually works, do I need to multiply the days part of the 300 for the WORKDAY by 1.4 to include the weekend though, or does the WORKDAY function already take the weekend into account ?

 

[Fluff]

Workday ignores Sat & Sun.

 

[ExcelNoobUser]

Workday ignores Sat & Sun.

Apologies, the youtube tutorial I used said to include the weekends in the holidays because they said the function does not exclude weekends, unfortunately I didn't do a double check at the time to see if it was true, I removed the weekends I added at the bottom of the holidays in the M column and checked the Stop Date and they seem to be accurate, thank you for clarifying that the weekends are ignored

 

[Fluff]

You're welcome & thanks for the feedback.