Skip weekend days on production roster

**SkeptiCol** · 01-24-2011, 11:18 PM

I want to do a roster that looks at the date in a month, and will only deduct the average production quantity if it is a weekday.

Here is my formula
=IF(C1=OR(WEEKDAY(6),WEEKDAY(7)),B14,(B14-$N$26))

C1 contains the date 1/1/11
C2 contains the day (Mon, Tue) derived from C1
B14 contains the estimated production qty for the month
N26 contains the average daily production qty

It won't detect sat/Sun and deducts when it should skip.

Thanks in advance for your help.

Skepticol

**teylyn** · 01-24-2011, 11:30 PM

Please don't expect us to recreate your file. It would be so much easier to understand what you are doing if we could see your workbook, so ....

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.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

**excelhariharan** · 01-24-2011, 11:39 PM

Since C2 contains the name of the days like sunday, monday. You can write the formula like this.
=IF(OR(C2="Saturday", C2="Sunday"),B14,(B14-$N$26))
Hope this helps....

**SkeptiCol** · 01-25-2011, 01:59 PM

I have attached a sheet that i hope explains what i'm after a bit better.

**SkeptiCol** · 01-25-2011, 02:05 PM

Originally Posted by excelhariharan

Since C2 contains the name of the days like sunday, monday. You can write the formula like this.
=IF(OR(C2="Saturday", C2="Sunday"),B14,(B14-$N$26))
Hope this helps....

I just gave this a try, but it continues to ignore the day. Frustrating.

Thanks

**teylyn** · 01-25-2011, 08:15 PM

Well,

Here is my formula
=IF(C1=OR(WEEKDAY(6),WEEKDAY(7)),B14,(B14-$N$26))

this is not your formula at all, at least, I can't find that one anywhere.

I can find

=IF(C1=OR(WEEKDAY(C1,16)=6,WEEKDAY(C1,16)=7),B13,(B13-$F$21))

C1 is a date. Weekday(C1) delivers a number from 1 to 7. C1 can never be = Weekday(C1), so drop that comparison

=IF(OR(WEEKDAY(C1,16)=6,WEEKDAY(C1,16)=7),B13,(B13-$F$21))

Using Weekday with the 16 as the second parameter delivers a 1 for Saturday, 2 for Sunday, etc, so you need to compare with 1 and 2 to rule out weekends

=IF(OR(WEEKDAY(C1,16)=1,WEEKDAY(C1,16)=2),B13,(B13-$F$21))

Now, use this in C13 and copy the formula to the right and all will be good.

cheers

**SkeptiCol** · 01-25-2011, 09:16 PM

Thanks for the solve!!

Sorry for the sheet, did a copy and paste to a new sheet as the original was a mammoth, and didn't include the formula as i had it wrong anyway.

SAA-WEET.