Workdays excluding Saturday, Sunday & holidays country wise dynamic selection

muzaffarsiddiqui

New Member
Joined
Sep 10, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi

I have made the excel project plan and it is working fine but I have to select holidays manually by seeing the country of the assignee. I want this to be done as per the countries defined in the project plan by seeing the list of per country holidays from the field value of the assignee location. In other words, I am defining country name with each date and want to dynamically look at the holiday's list where information is populated as country and holiday date, the country will be repeated with each holiday. There will be more than 3 countries on the holiday list.

I am using this formula to calculate the start and end dates of each task:

=WORKDAY(E12,(F12),Holidays!$B$5:$B$8)

I have tried to change the above formula by adding different conditions but nothing works.

I am also looking to protect/lock formula cells dynamically, i.e., when we defined formula. Is it possible?

I am also looking to have different colors for formula fields dynamically. I don't know whether it is possible?

I have lost a lot of my time to sort out this formula but none was successful. Your kind help is appreciated.

Thank you in advance for the effort!
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,026
Office Version
  1. 365
Platform
  1. Windows
I've set out an example of what I think is the best way to do what you need for the first part of your question (the workday formula), although it might be preferable to have the table of holidays in a different sheet.

Locking formula cells dynamically could be done with vba but I would advise doing it manually instead (if you make an error when entering the formula then locking it dynamically will mean that you then have to unlock the sheet to correct it).

The colouration part is open to interpretation, without a more detailed example of what is required the best thing that I can suggest is to look at conditional formatting.

Book1
ABCDEFG
4Country 1Country 2country 3
514/09/202014/09/202023/09/2020
615/09/2020
7
8
9
10
11
12Country 110/09/2020722/09/2020
13Country 210/09/2020723/09/2020
14Country 310/09/2020721/09/2020
Sheet4
Cell Formulas
RangeFormula
G12:G14G12=WORKDAY(E12,F12,INDEX($A$5:$C$8,0,MATCH(D12,$A$4:$C$4,0)))
 

deciog

Board Regular
Joined
Mar 26, 2016
Messages
59
Office Version
  1. 365
Check if it is this way

=WORKDAY(E12,(F12-1),IF(TRIM(Holidays!$A$5:$A$24)=D12,Holidays!$B$5:$B$24,0))

Decio
 

Attachments

  • Moddelo.png
    Moddelo.png
    8.9 KB · Views: 12
  • Holiday.png
    Holiday.png
    14.9 KB · Views: 11

Watch MrExcel Video

Forum statistics

Threads
1,123,492
Messages
5,601,994
Members
414,490
Latest member
Rip181

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