If I have two dates (i.e. hire and terminated dates), how do I calculate the
number of pay periods if the person was paid the 1st and 15th of every month?

Hi janplan
1 day A1
2 day B1
Try
=SUMPRODUCT((DAY(ROW(INDIRECT($A$1&":"&$B$1)))=1)+ (DAY(ROW(INDIRECT($A$1&":"&$B$1)))=15))

Regards,
Pedro J.


If I have two dates (i.e. hire and terminated dates), how do I calculate the
number of pay periods if the person was paid the 1st and 15th of every month?

That can be reduced to:

=SUMPRODUCT(--(DAY(ROW(INDIRECT(A1&":"&B1)))={1,15}))

--
Biff
Microsoft Excel MVP


"Infinitogool" wrote in message
...
Hi janplan
1 day A1
2 day B1
Try
=SUMPRODUCT((DAY(ROW(INDIRECT($A$1&":"&$B$1)))=1)+ (DAY(ROW(INDIRECT($A$1&":"&$B$1)))=15))

Regards,
Pedro J.


If I have two dates (i.e. hire and terminated dates), how do I calculate
the number of pay periods if the person was paid the 1st and 15th of
every month?

Hi,
Jumping in...
Could one of you plesae tell me why we need the <INDIRECT(A1&":"&B1) part
of the formula, and why we can't just use <(A1:B1).
Also, when I highlight <INDIRECT(A1&":"&B1 ) in the formula bar and press
F9, I get an error message which says "Formula is too long. Formulas may not
exceed 8192 characters."
Also, could you please explain what the ROW function is doing?

Regards - Dave.

Ok, let's see what this formula is doing.

=SUMPRODUCT(--(DAY(ROW(INDIRECT(A1&":"&B1)))={1,15}))

We want to count how many 1st and 15th days of the months there are between
a start date and an end date (inclusive).

In order to understand how this formula works you must first understand how
Excel processes dates. Dates are just numbers formatted to look like dates.
In Excel each individual day has a value of 1. The actual date is calculated
by starting from a default base date and then just adding 1 for each
successive day that has passed. By default Excel uses January 1 1900 as the
base date. So, the numeric value for January 1 1990 is 1. This numeric value
is also known as a the date serial number/value.

Since each individual day has a value of 1 we can calculate the date by
counting the number of days since the base date of January 1 1900
(inclusive). Date serial number 1 is January 1 1900, so, date serial number
15 would be January 15 1900. Date serial number 5000 is September 8 1913.
Today is August 9 2008, its date serial number is 39,669. 39,669 days since
January 1 1900 (inclusive).

You can see this by doing the following:

Enter a date in cell A1. By default Excel applies formatting and displays
the date as a DATE. To see its true value, the unformatted date serial
number, change the format of cell A1 to General.

Ok, now let's see how these dates are manipulated in the formula.

=SUMPRODUCT(--(DAY(ROW(INDIRECT(A1&":"&B1)))={1,15}))

You specifically asked why we need to use INDIRECT. We need to pass the date
serial numbers to the DAY function so it can evaluate them and then in turn
have SUMPRODUCT count how many are the 1st and 15th.

We have a start date and an end date but we also need every individual date
inbetween. Let's assume our dates a

A1 = start date = 2/27/2008
B1 = end date = 8/4/2008

So, we need to pass to the DAY function an *array* of dates from 2/27/2008
to 8/4/2008. We do that using the ROW function. The ROW function returns an
*array* based on its argument. If you just used:

ROW(A1:B1)

The final result of the formula would be 1. ROW returns an *array* of
numbers based on the *row* number(s) of its argument. In the above, the
returned array would be {1} since the evaluated argument A1:B1 comprises
just the single row, 1. This is where INDIRECT comes into play.

INDIRECT(A1&":"&B1)

INDIRECT takes the dates in A1 and B1 and converts them into a usable
argument that ROW can then evaluate. With our dates:

A1 = 2/27/2008 = date serial number = 39505
B1 = 8/4/2008 = date serial number = 39664

INDIRECT evaluates to: INDIRECT("39505:39664") and converts that TEXT
representation of an argument and passes it to the ROW function so that ROW
evaluates as: ROW($39505:$39664). It's important to know that the argument
passed to ROW must be a valid argument. ROW can't use any value that is
smaller than 1 or larger than the total number of rows an Excel worksheet
can have.

So now we have a means of passing the *array* of dates to the DAY function:

DAY(ROW($39505:$39664)) which evaluates to:

DAY({39505;39506;39507;39508;....39664})

DAY then evalautes these serial numbers and returns an array of day of the
month numbers like this:

DAY(39505) = 27 (2/27/2008)
DAY(39506) = 28 (2/28/2008)
DAY(39507) = 29 (2/29/2008)
DAY(39508) = 1 (3/1/2008)
...
...
DAY(39664) = 4 (8/4/208)

DAY({27;28;29;1;.....4})

This array of day numbers is then evalauted to be equal to 1 or 15

=SUMPRODUCT(--({27;28;29;1;.....4}={1,15}))

This evaluation returns an array of either TRUE or FALSE:

27 = {1,15} = F,F
28 = {1,15} = F,F
29 = {1,15} = F,F
1 = {1,15} = T,F
etc etc

Since SUMPRODUCT can't use these logical values we need to convert them to
numbers which SUMPRODUCT then sums to arrive at our final result. One way to
convert the logicals to numbers is to use the double unary operator "--". It
coerces TRUE to 1 and FALSE to 0.

We now have:

=SUMPRODUCT({0,0;0,0;0,0;1,0;...etc})

So:

A1 = 2/27/2008
B1 = 8/4/2008

=SUMPRODUCT(--(DAY(ROW(INDIRECT(A1&":"&B1)))={1,15}))

=11

Also, when I highlight <INDIRECT(A1&":"&B1 ) in the
formula bar and press F9, I get an error message which says
"Formula is too long. Formulas may not exceed 8192 characters."

You must be using Excel 2007 and a large date span. Use a smaller date span.
Try it with a span of just a "few" days and then it'll work.


--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
Hi,
Jumping in...
Could one of you plesae tell me why we need the <INDIRECT(A1&":"&B1) part
of the formula, and why we can't just use <(A1:B1).
Also, when I highlight <INDIRECT(A1&":"&B1 ) in the formula bar and press
F9, I get an error message which says "Formula is too long. Formulas may
not
exceed 8192 characters."
Also, could you please explain what the ROW function is doing?

Regards - Dave.

That is a pretty good explanation. I need to "mark" this thread for future
use so I won't have to type that all over again!


exp101
--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Ok, let's see what this formula is doing.

=SUMPRODUCT(--(DAY(ROW(INDIRECT(A1&":"&B1)))={1,15}))

We want to count how many 1st and 15th days of the months there are
between a start date and an end date (inclusive).

In order to understand how this formula works you must first understand
how Excel processes dates. Dates are just numbers formatted to look like
dates. In Excel each individual day has a value of 1. The actual date is
calculated by starting from a default base date and then just adding 1 for
each successive day that has passed. By default Excel uses January 1 1900
as the base date. So, the numeric value for January 1 1990 is 1. This
numeric value is also known as a the date serial number/value.

Since each individual day has a value of 1 we can calculate the date by
counting the number of days since the base date of January 1 1900
(inclusive). Date serial number 1 is January 1 1900, so, date serial
number 15 would be January 15 1900. Date serial number 5000 is September 8
1913. Today is August 9 2008, its date serial number is 39,669. 39,669
days since January 1 1900 (inclusive).

You can see this by doing the following:

Enter a date in cell A1. By default Excel applies formatting and displays
the date as a DATE. To see its true value, the unformatted date serial
number, change the format of cell A1 to General.

Ok, now let's see how these dates are manipulated in the formula.

=SUMPRODUCT(--(DAY(ROW(INDIRECT(A1&":"&B1)))={1,15}))

You specifically asked why we need to use INDIRECT. We need to pass the
date serial numbers to the DAY function so it can evaluate them and then
in turn have SUMPRODUCT count how many are the 1st and 15th.

We have a start date and an end date but we also need every individual
date inbetween. Let's assume our dates a

A1 = start date = 2/27/2008
B1 = end date = 8/4/2008

So, we need to pass to the DAY function an *array* of dates from 2/27/2008
to 8/4/2008. We do that using the ROW function. The ROW function returns
an *array* based on its argument. If you just used:

ROW(A1:B1)

The final result of the formula would be 1. ROW returns an *array* of
numbers based on the *row* number(s) of its argument. In the above, the
returned array would be {1} since the evaluated argument A1:B1 comprises
just the single row, 1. This is where INDIRECT comes into play.

INDIRECT(A1&":"&B1)

INDIRECT takes the dates in A1 and B1 and converts them into a usable
argument that ROW can then evaluate. With our dates:

A1 = 2/27/2008 = date serial number = 39505
B1 = 8/4/2008 = date serial number = 39664

INDIRECT evaluates to: INDIRECT("39505:39664") and converts that TEXT
representation of an argument and passes it to the ROW function so that
ROW evaluates as: ROW($39505:$39664). It's important to know that the
argument passed to ROW must be a valid argument. ROW can't use any value
that is smaller than 1 or larger than the total number of rows an Excel
worksheet can have.

So now we have a means of passing the *array* of dates to the DAY
function:

DAY(ROW($39505:$39664)) which evaluates to:

DAY({39505;39506;39507;39508;....39664})

DAY then evalautes these serial numbers and returns an array of day of the
month numbers like this:

DAY(39505) = 27 (2/27/2008)
DAY(39506) = 28 (2/28/2008)
DAY(39507) = 29 (2/29/2008)
DAY(39508) = 1 (3/1/2008)
..
..
DAY(39664) = 4 (8/4/208)

DAY({27;28;29;1;.....4})

This array of day numbers is then evalauted to be equal to 1 or 15

=SUMPRODUCT(--({27;28;29;1;.....4}={1,15}))

This evaluation returns an array of either TRUE or FALSE:

27 = {1,15} = F,F
28 = {1,15} = F,F
29 = {1,15} = F,F
1 = {1,15} = T,F
etc etc

Since SUMPRODUCT can't use these logical values we need to convert them to
numbers which SUMPRODUCT then sums to arrive at our final result. One way
to convert the logicals to numbers is to use the double unary operator
"--". It coerces TRUE to 1 and FALSE to 0.

We now have:

=SUMPRODUCT({0,0;0,0;0,0;1,0;...etc})

So:

A1 = 2/27/2008
B1 = 8/4/2008

=SUMPRODUCT(--(DAY(ROW(INDIRECT(A1&":"&B1)))={1,15}))

=11

Also, when I highlight <INDIRECT(A1&":"&B1 ) in the
formula bar and press F9, I get an error message which says
"Formula is too long. Formulas may not exceed 8192 characters."

You must be using Excel 2007 and a large date span. Use a smaller date
span. Try it with a span of just a "few" days and then it'll work.


--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
Hi,
Jumping in...
Could one of you plesae tell me why we need the <INDIRECT(A1&":"&B1)
part
of the formula, and why we can't just use <(A1:B1).
Also, when I highlight <INDIRECT(A1&":"&B1 ) in the formula bar and
press
F9, I get an error message which says "Formula is too long. Formulas may
not
exceed 8192 characters."
Also, could you please explain what the ROW function is doing?

Regards - Dave.

In case the OP wants to consider a non-volatile, non-array-based formula...

=2*(DATEDIF(A1-DAY(A1)+1,B1-DAY(B1)+1,"m")-1)+(DAY(A1)=1)+(DAY(A1)<=15)+(DAY(B1)=1)+(DAY(B1) =15)

Rick


"T. Valko" wrote in message
...
That can be reduced to:

=SUMPRODUCT(--(DAY(ROW(INDIRECT(A1&":"&B1)))={1,15}))

--
Biff
Microsoft Excel MVP


"Infinitogool" wrote in message
...
Hi janplan
1 day A1
2 day B1
Try
=SUMPRODUCT((DAY(ROW(INDIRECT($A$1&":"&$B$1)))=1)+ (DAY(ROW(INDIRECT($A$1&":"&$B$1)))=15))

Regards,
Pedro J.


If I have two dates (i.e. hire and terminated dates), how do I calculate
the number of pay periods if the person was paid the 1st and 15th of
every month?

1. Determine the pay periods: In this case, the pay periods are on the 1st and 15th of every month.
2. Calculate the number of months between the hire and terminated dates: You can use the DATEDIF function in Excel to calculate the number of months between two dates. For example, if the hire date is in cell A1 and the terminated date is in cell B1, you can use the formula =DATEDIF(A1,B1,"m").
3. Multiply the number of months by 2: Since there are two pay periods per month, you need to multiply the number of months by 2 to get the total number of pay periods.
4. Adjust for partial pay periods: If the hire or terminated date falls in the middle of a pay period, you need to adjust the total number of pay periods accordingly. For example, if the hire date is on the 5th of the month, you would only count one pay period for that month.
5. Subtract any skipped pay periods: If the terminated date falls before the next pay period, you would not count that pay period. For example, if the terminated date is on the 10th of the month, you would not count the pay period on the 15th.
   
   Here's an example formula that combines these steps:
   
   
   Formula:

   =IF(DAY(A1)<=15,2,1)+IF(DAY(B1)=15,2,1)+DATEDIF(A1,B1,"m")*2-IF(DAY(A1)15,1,0)-IF(DAY(B1)<15,1,0) 

   This formula assumes that the hire date is in cell A1 and the terminated date is in cell B1. It first checks if the hire date is before or after the 15th of the month, and adds 1 or 2 pay periods accordingly. It does the same for the terminated date. It then calculates the number of months between the two dates and multiplies by 2. Finally, it adjusts for any partial pay periods by subtracting 1 if the hire date is after the 15th and subtracting 1 if the terminated date is before the 15th.