Adjust holiday (Xmas) break in between the project.

bentxoxo

New Member
Joined
Jan 13, 2016
Messages
29
Hi Everyone,


I will be great-full if someone can help me with the formula or macro on this.


We have tasks assigned to each department with the due date. The tasks have been distributed in the order that they need to be completed. This is done so that each department completes the tasks in the specified time.


For example:
Project Brief (Sales) Due date 15/08/2019, Days before project delivery -127
Project Costing (Finance) Due date 20/09/2019 -91
Project Approval (Management) Due date 18/10/2019 -63
Order received (Sales) Due date 30/10/2019 -51
Goods ordered (Logistics) Due date 10/11/2019 -40
Stock received (Logistics) Due date 30/11/2019 -20
Payment received (Finance) Due Date 10/12/2019 -10
Stock supplied (Transportation) Due Date 20/12/2019 0
Project reporting (Sales) Due Date 30/01/2020 +41


Project delivery / completion dates is based on when the stock needs to be supplied to the customer. For example if the project delivery date is 20/12/2019. We should have received payment 10 days before this date and stock from supplier 20 days before so that we can achieve the target date.


Each project has different stock supplied date and if for example, supplied date is 20/Feb most of the tasks fall into Xmas period between 21/Dec/2019 to 10/Jan/2020. Is it possible for someone to do a formula or macro that all the dates falling in between Xmas holiday period automatically moved before that and keep same number of days difference between the tasks.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
When you're saying "days" do you mean "workdays" or days in general ( = including weekends)? If it's workdays you're talking about you can use the WORKDAY formula. The formula syntax is =WORKDAY (start_date, days, [holidays]). Just have all the days you want to exclude from the period in a range and this simple formula is all you need.

If you want to include weekends in your number of days you'd better have Excel 2010 or later so you can use the WORKDAY.INTL function. The syntax for this is =WORKDAY.INTL (start_date, days, [weekend], [holidays]. Just give it "0000000" as the weekend argument and you're good to go.

If you're using older version of Excel you're going to have to use SUMPRODUCT or other array formulas to reduce the number of saturdays and sundays to your number of days.
 
Upvote 0
Hi Misca, Appreciate your reply and I really apologise for not getting back to you earlier.

https://www.dropbox.com/s/02sqdqreiknm1b5/Tmp-Mst01.xls?dl=0

I have uploaded a excel file with some examples and I hope you will be able to access it.

In that file I have added conditional formating if you change the delivery date it will highlight Xmas holiday period. No other holidays need to be considered.
If any of the date falling in the Xmas holiday season the Macro / formula should calculate date for the corresponding column without taking the Xmas holidays.

Thanks in advance.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,924
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top