Excluding multiple holidays

[Suresh Aruchamy]

Dear there Greetings..!

This is about the excluding the weekends for global countries.

Sheet 1 I have column names Ticket ID, country name, Start Date, End date, Age (After Excluding Weekends). And it has about 6000 (tickets) rows which are from various global countries (Some Examples: India, UAE, Russia, Yemen, South Africa and Brazil). The main problem here is that , the different global countries has different weekends like South Asia countries has Saturday and Sunday, ISREAL & UAE has Friday and Saturday week off, IRAN & Saudi Arabia has Thursday& Friday week off.
So I need logic like, it should see the country name first, then decide the weekends and then exclude the days. Is this possible in formula?

Thanks for your help.

Regards,
Suresh

[FDibbins]

Hi, welcome to the forum

You dont really explain exactly what you want here ID the weekends - and do what?

Also, To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[Richard Buttrey]

Hi,

Does the attached example help.

It uses the NETWORKDAYS.INTL() function with two tables for defining weekend dates and holiday dates.

[Suresh Aruchamy]

Dear FDibbins Greetings..!

This is about the excluding the weekends and Holidays for global countries.

Question: 1

Sheet 1 I have column names Ticket ID, country name, Start Date, End date, Age (After Excluding Weekends), Holiday Dates, Number of Holidays, Final Age. And it has about 6000 (tickets) rows which are from various global countries (Some Examples: India, UAE, Russia, Yemen, South Africa and Brazil). The main problem here is that , the different global countries has different weekends like South Asia countries has Saturday and Sunday, ISREAL & UAE has Friday and Saturday week off, IRAN & Saudi Arabia has Thursday& Friday week off.
So I need logic like, it should see the country name first, then decide the weekends and then exclude the days. Is this possible in formula?

Question: 2

Need VBA Here,

In Sheet 2 I have country wise holiday list which has 2014 January to 2015 December holidays. The data is like this:
Country Names Date1 Date2 Date3
Algeria 01/01/2014 01/19/2014 02/23/2014
Australia 01/01/2014 03/07/2014 04/01/2014
Brazil 01/12/2014 01/13/2014 02/26/2014

So I need VBA here which will full fill following criteria’s
1) Since in Sheet1 country name doesn’t have alphabetic order or Start Date and End date doesn’t sorted , the code should be do repeat work till the last row
2) 1n 6000 tickets one single country can come repeatedly with different start date and End date
3) Sometimes the holiday date and week end will be the same date, that time we should not exclude twice
4) Some tickets cover both the year as start date (06/25/2014) and End date (05/17/2015).
5) If we have multiple holidays then in sheet1 and in ”Holiday dates” column we have to print the holiday dates with comma (Like : 07/01/2014 , 07/12/2014, 08/15/2014 )
6) Then the “Number of holidays” column should have count as 3 (As per above example)
I know this is very tough, but there are legends who do this job in couple of minutes. So please help me.
Thanks for your help.

Regards,
Suresh

[Richard Buttrey]

Hi,

Have you tried adapting the approach I gave you in post #3. You have all the tables for holidays (just turn them through 90 degrees as per my example.

[Suresh Aruchamy]

Hi Richard Buttrey,

Thank you for your reply..

I have transpose the dates as per you, but It seems not working, I am getting Error like "#NAME?"