How to identify indian holiday dates in business data in one shot

[Kumar Shanmugam]

I have a business data which contains date of sale. I want to find the impact of sales on holiday dates. Problem is I am not able to identify the holiday dates in one shot. For eg I want a formula and function where if( sale_date=Jan252013,Jan262013,March102013,March272013,March292013 than Holiday= GoodFriday, Republic date, Mahashivratri, Holi)
Please assist,

Is there any excel formula? Thanks

 

[Hui]

you can use either an Match or Networkdays.intl functions to see if the date is in the list

Setup a list of Dates in a Range
apply the function


Note that each return a different value if it is a holiday or not
See formulas in the attached file

If you post a file with your data and what you want to achieve to make a more specific answer possible

 

[Kumar Shanmugam]

Hi thanks but this will formula will give me count of total holidays but will not provide me the count of business on GoodFriday, Republicday, Mahashivratri and other holidays.

 

[Hui]

I just gave you two examples of how it can be used, but there are many more

If you post a file with your data and what you want to achieve it will allow us to make a more specific answer possible

 

[Kumar Shanmugam]

please find the attached file which contains sale date and against sale date I want type of holiday for eg pongal. Holiday details is in second and third sheet

 

[Hui]

Saledate!B2:
=IF(YEAR(A2)=2013,IFERROR(IF(MATCH(A2,'2013holiday'!$A$2:$A$46,0),INDEX('2013holiday'!$C$2:$C$46,MATCH($A2,'2013holiday'!$A$2:$A$46,0)),""),""),IFERROR(IF(MATCH(A2,'2014holiday'!$A$2:$A$81,0),INDEX('2014holiday'!$C$2:$C$81,MATCH($A2,'2014holiday'!$A$2:$A$81,0)),""),""))
copy down

If you just have 1 list of holidays in the 2103Holiday worksheet and copy/paste the 2014 holidays at the bottom of the 2013 list,
You can use this much shortened formula:

=IFERROR(IF(MATCH(A2,'2013holiday'!$A$2:$A$126,0),INDEX('2013holiday'!$C$2:$C$126,MATCH($A2,'2013holiday'!$A$2:$A$126,0)),""),"")
copy down

 

[Kumar Shanmugam]

Thanks it was amazing it worked but formula is little bit difficult to understand. I am trying to learn this formula

 

[prasaddn]

Hi,

Alternatively,

you can use vlookup() and to supress #N/A pass the result with IFerror().
example:
=IFERROR(VLOOKUP(A7,'2013holiday'!A2:C126,3,0),"")
Regards,
Prasad DN

 

[Kumar Shanmugam]

Thanks this is much more simpler