Find holiday ,sunday and saturday and replace

Hello check this pic http://imgur.com/VEW4khL
I have all dates from 1 jan 2011 to 31/ dec 2015 in column "B"
I have holidays in column "F"

I want a formula to find these holiday dates of column "F" in column "B" and write "2" in adjoining column "C" if there is holiday other wise "1"
I also want "2" in column "C" if there is sunday or saturday in column "C" dates other wise "1"

Result I want see pic http://imgur.com/wRAnusN
check result I want in column "C"
I want nothing in column "D",I have given this in pic just for illustration

Greetings,
Using Excel 2013 and based on your example the solution for column C will be as follows:
=IF(WEEKDAY(a1, 2)>5, "2", "1")

You could use the same principle to match the Holidays on a nested IF.

Hope this helps

thanks this formula works in list in above post but not in this list where dates are random http://imgur.com/TaFKkxJ
also how can I add formula for holiday ?

Dear lightofchaos
I tried the ans but it didn't work... Sorry i think i didn't able to use the formula properly. So Please upload an example sheet if it is possible it will help us very much....


Alternatively in column no C this formula can be used =IF(COUNTIF(F:F,B2)=1,2,IF(OR(TEXT(B2,"DDDD")="Sunday",TEXT(B2,"DDDD")="Saturday"),2,1))

and in column D this formula can be used
=IF(C2=2,IFERROR(VLOOKUP(B2,I:J,2,FALSE),TEXT(B2,"DDDD")),"")

Dear lightofchaos
I tried the ans but it didn't work... Sorry i think i didn't able to use the formula properly. So Please upload an example sheet if it is possible it will help us very much....


Alternatively in column no C this formula can be used =IF(COUNTIF(F:F,B2)=1,2,IF(OR(TEXT(B2,"DDDD")="Sunday",TEXT(B2,"DDDD")="Saturday"),2,1))

and in column D this formula can be used
=IF(C2=2,IFERROR(VLOOKUP(B2,I:J,2,FALSE),TEXT(B2,"DDDD")),"")

Greetings $hivasopa,

Could you please step me through the upload process as I'm fairly new to this.

Thank you,

Using the above formulas you will get the following result.
1/1/2015 Thursday 1 1/3/2015
1/2/2015 Friday 1 1/10/2015
1/3/2015 Saturday 2 2 1/26/2015
1/4/2015 Sunday 2
1/5/2015 Monday 1
1/6/2015 Tuesday 1
1/7/2015 Wednesday 1
1/8/2015 Thursday 1
1/9/2015 Friday 1
1/10/2015 Saturday 2 2
1/11/2015 Sunday 2
1/12/2015 Monday 1
1/13/2015 Tuesday 1
1/14/2015 Wednesday 1
1/15/2015 Thursday 1
1/16/2015 Friday 1
1/17/2015 Saturday 2
1/18/2015 Sunday 2
1/19/2015 Monday 1
1/20/2015 Tuesday 1
1/21/2015 Wednesday 1
1/22/2015 Thursday 1
1/23/2015 Friday 1
1/24/2015 Saturday 2
1/25/2015 Sunday 2
1/26/2015 Monday 1 2
1/27/2015 Tuesday 1
1/28/2015 Wednesday 1
1/29/2015 Thursday 1
1/30/2015 Friday 1

thanks both
lightofchaos use goadvance for uploading attachment
$shivasopa I want "2" for holidays and sunday ,friday not the text

I am uploading the file with alternative answers use the formula as per your requirement

i think it will help you kevin....

Originally Posted by $hivasopa

I am uploading the file with alternative answers use the formula as per your requirement

i think it will help you kevin....

thanks $hivasopa ,but this file do not give "2" for sunday and holiday
I want "2" for sunday,saturday and holiday else "1"

Originally Posted by kavin

thanks both
lightofchaos use goadvance for uploading attachment
$shivasopa I want "2" for holidays and sunday ,friday not the text

Thanks for the feedback. Here is the attached example

Originally Posted by lightofchaos

Thanks for the feedback. Here is the attached example

thanks lightofchaos ,this is what I want ,but I want both result in column "C"

Originally Posted by kavin

Hello check this pic http://imgur.com/VEW4khL
I have all dates from 1 jan 2011 to 31/ dec 2015 in column "B"
I have holidays in column "F"

I want a formula to find these holiday dates of column "F" in column "B" and write "2" in adjoining column "C" if there is holiday other wise "1"
I also want "2" in column "C" if there is sunday or saturday in column "C" dates other wise "1"

Result I want see pic http://imgur.com/wRAnusN
check result I want in column "C"
I want nothing in column "D",I have given this in pic just for illustration

check attached sheet

Kavin use this formula i think it will help you
=IF(COUNTIF(F:F,$B2)=1,VLOOKUP($B2,F:G,2,FALSE),IF(OR(TEXT($B2,"DDDD")="Saturday",TEXT($B2,"DDDD")="Sunday"),TEXT($B2,"DDDD"),""))

Greetings,
For the second part of the question (Holidays). You need to have a point of reference. You could use the If statement with Match, using the same criteria (F column as point of reference)as follows:

=IF(MATCH(A1,$F$1:$F$3, 0), "2"," ")

Hope this helps.

Greetings,

From earlier example the following formula will do the trick (Please note I've only used three holidays as an example from column F.

=IFERROR(IF(MATCH(A3,$F$1:$F$3, 0), "2"," "), "")

Hope this works.

Originally Posted by kavin

Hello check this pic http://imgur.com/VEW4khL
I have all dates from 1 jan 2011 to 31/ dec 2015 in column "B"
I have holidays in column "F"

I want a formula to find these holiday dates of column "F" in column "B" and write "2" in adjoining column "C" if there is holiday other wise "1"
I also want "2" in column "C" if there is sunday or saturday in column "C" dates other wise "1"

Result I want see pic http://imgur.com/wRAnusN
check result I want in column "C"
I want nothing in column "D",I have given this in pic just for illustration

check attached sheet

best reply lightofchaos
but the que make it very typical even it is very easy...
Thanks

thanks this solves