Simple IF/Or function

[hambly]

I thought this would be straight forward.

Column A is the date. Column B = Column A but in DDDD format. I want column C to give me a value based off column B.

If column B = "Saturday" or "Sunday" then column C is 8. If any other day, then 9.
My If/Or formula works if column B is just text, but if it's in DDDD format it won't work..

Sample file attached.

Workbook3.xlsx

Thanks for your help.
Rob

[Fotis1991]

Try

=IF(OR(TEXT(B2,"dddd")="Sunday",TEXT(B2;"dddd")="Saturday"),8,9)

[martindwilson]

or
=MIN(FLOOR(WEEKDAY(A1,2),6)+8,9)

[hambly]

Thanks both.

Fotis. Yours worked after I fixed the semi-colon to a comma after the 2nd B2...

Thanks for your method Martin. Something new for me to add to my Excel knowledge..

[Fotis1991]

You are welcome

We use semi colons as separators in Greece. So i have always to change the semi colons of my formulas to comma.

Unfortunately sometimes, i forget some of these!

[Teethless mama]

Another one, but shorter.......

=9-(WEEKDAY(A2,2)>5)

[hambly]

Thanks Mama! That works too.. but I have no idea how?!

[Teethless mama]

You're Welcome!

[dredwolf]

i think i can explain how Mama's formula works:

the Weekday Function will return a a value of 1-7, with 6 and 7 being the weekend,
so if it returns a value larger than 5 (TRUE = 1) the formula reads 9-1 = 8 (Sat or Sun)
but if it returns a value <=5 (FALSE = 0) the formula reads 9-0 = 9 (Mon-Fri)

hope that helps !

[martindwilson]

ive just realised my solution is the wrong way around"
so
=MIN(ABS(FLOOR(WEEKDAY(A1,2),6)-7)+7,9)
but tm's
=9-(WEEKDAY(A2,2)>5) is better

[hambly]

Thanks again all. I like the simplicity of Mama's, but I also like that =IF(OR(TEXT(B2,"dddd")="Sunday",TEXT(B2;"dddd")="Saturday"),8,9) has a visual of the actual values (8 or 9) as I'd like to change these values every once in a while...