Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Is it possible to exlcude holidays as per respective country while calcualting lead time between start & end date .
So sample data as given below.
Order No | Country | Start Date | End Date | Lead time |
123 | UK | 01-01-20 | 20-01-20 | 11 |
234 | Canada | 02-01-20 | 21-01-20 | 11 |
456 | US | 03-01-20 | 22-01-20 | 13 |
678 | UK | 04-01-20 | 23-01-20 | 13 |
789 | US | 05-01-20 | 24-01-20 | 14 |
890 | Canada | 06-01-20 | 25-01-20 | 14 |
Holiday Table -
Country | Holiday |
UK | 01-01-20 |
UK | 02-01-20 |
UK | 06-01-20 |
US | 01-01-20 |
US | 02-01-20 |
US | 06-01-20 |
Canada | 03-01-20 |
Canada | 02-01-20 |
Canada | 10-01-20 |
I want to deduct country specific holidays as per second table. Is there any way to do so?
Thanks.
I would do:
create three variables:
holidayUK = '01.01.2020','02.01.2020','06.01.2020'
holidayCD = '01.01.2020','02.01.2020','06.01.2020'
holidayUS = '02.01.2020','03.01.2020','10.01.2020'
then in script:
if(Country='US',networkdays,startdate,enddate,$(holidayUS),
if(Country='UK',networkdays,startdate,enddate,$(holidayUK),
if(Country='Canada',networkdays,startdate,enddate,$(holidayCD),networkdays,startdate,enddate))) as Networkdays
Regards
Thank you for your reply.
Actually, it's just a sample data. I have a complete database table for country holidays. THis table has around 50 countries with holidays. That will need to use.
Any way to use table for holiday list.