Work Days Formulae on Estimating Days

midfieldgeneral11

Active Member
Joined
Feb 11, 2004
Messages
460
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
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,467
Office Version
  1. 365
Platform
  1. Windows
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.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,467
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Feb 11, 2004
Messages
460
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

Well-known Member
Joined
Dec 30, 2008
Messages
12,467
Office Version
  1. 365
Platform
  1. Windows
Either enter a negative number of days in B1, or change the formula to =WORKDAY(A1,-B1)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,618
Messages
5,625,888
Members
416,141
Latest member
Bartek9q

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top