to find out number off offdays of each employee

[pol]

I have to find the number of offdays depending upon the values of two cell
A1 cell
and B1 cell
A1 ( Saturday) cell contain 1,2,3,4
B1 cell contain number of off days in January 2008.
This is the active cell. Here I have to find out the number of off days by
calculating the value of A1cell.
If A1 cell = 1 that means every Saturdays will be offdays. Here I will
apply the formula
num_sat=(WEEKDAY(ROW(INDIRECT(DATE(2008,1,1)&":"&DATE(2008,1,31))),1)=7)) .
I will get number of saturdays

If the value is 2 then the off day will be every 2th Saturdays. For
example in January 2008 the offday will be in this case .

12,26 so number of Saturdays offday in January month will be 2

If the value is 3 then offday will be evey 3rd Saturdays For example in
January 2008 the offday will be in this case .

Offday will be 19 so the number of offday will be 1

So how I can find out the number of offday using the formula
WEEKDAY(ROW(INDIRECT(DATE(2008,1,1)&":"&DATE(2008,1,31))),1)=7)) and to write
in B1 cell

Please help
with thanks
Pol

 

[ShaneDevenshire]

Hi,

You could try this:

=SUMPRODUCT(--(MOD(ROW(INDIRECT(DATE(2008,1,1)&":"&DATE(2008,1,31))),7*A1)=0))

Where A1 is a number from 1 to 4,

You also might make the formula a little more dynamic by entering the first
of the month in C1 and then

=SUMPRODUCT(--(MOD(ROW(INDIRECT(C1&":"&EOMONTH(C1,0))),7*A1)=0))

The EOMONTH function returns the end of the month is this case the current
(0) month. This is an analysis toolpak function so you must attach it via
Tools, Add-ins, check Analysis ToolPak.

If this helps, please check the Yes button.