Work Days Formulae on Estimating Days

[midfieldgeneral11]

Work Days Formulae on Estimating Days - Hello I am trying to plan work and would like to have two formulaes. can you help I have been racking my brains

I get asked two things on working forward and backwards when it comes to getting Start Date and End Dates for rough high end estimates I get

Need a formulae which allows me to review the following

Formulae 1 - Start Date Formuale

Column A, cell A1 is a Date I put a Start Date - 11/06/2020
Column B, Cell B1 is estimated work days to complete a piece of work - 2
Column C, Cell C1 will be a Delivery Date populated based on the calculation of the above excluding, weekends of Saturday and Sunday and bank holidays.
So the answer would be 13/06/2020

But it is harder when you are placing down 162 days.

Formulae 2 - End Date Formulae
it is working backwards......So I want to plan a end date of a delivery but I want to know based on the estimate when the
Column A, cell A3 is a Date I put a End Date of - 31/03/2020
Column B, Cell B3 is estimated work days to complete a piece of work - 2
Column C, Cell C3 will be a Start Date populated based on the calculation of the above excluding, weekends of Saturday and Sunday and bank holidays.
So the answer would be 29/03/2020

But again it is harder when you are placing down 150 days and working back

Thanks for your help on this.

S

 

[jasonb75]

Try the workday function

=WORKDAY(A1,B1)

Not that excel does not have a built in list of public / national / bank holidays as they vary by country. You will need to create your own list of these, see the excel help on the function for more information.

 

[midfieldgeneral11]

Sorry that does not work

 

[jasonb75]

It works fine for me, in what way does it not work for you?

Formula error? If so, what error?
Unexpected result? If so what date did you have in A1? what was the number of days in B1? what result were you expecting? Did you add your list of holidays as per the information in the help file?

 

[midfieldgeneral11]

Sorry long day. So if I wanted to do it the other way wanted to put a date in and go backwards. So end date is 31/12/2020 and I wanted to see the date of 180 days prior in workdays.
How would that work? can you help?

 

[jasonb75]

Either enter a negative number of days in B1, or change the formula to =WORKDAY(A1,-B1)