Counting number of pay period dates between 2 numbers
 

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?

Answer: Counting number of pay period dates between 2 numbers
 

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.

Counting number of pay period dates between 2 numbers
 

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?

Counting number of pay period dates between 2 numbers
 

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?

Counting number of pay period dates between 2 numbers
 

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.

Counting number of pay period dates between 2 numbers
 

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.

Counting number of pay period dates between 2 numbers
 

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.

Counting number of pay period dates between 2 numbers
 

Hi Biff,
Yes, a VERY good explanation. Thanks for your time in doing it.
Regards - Dave.

PS. How do you "mark a thread"?

"T. Valko" wrote:

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!

Counting number of pay period dates between 2 numbers
 

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?

Counting number of pay period dates between 2 numbers
 

You're welcome!

PS. How do you "mark a thread"?

Above my sig I put a little "marker", "exp101".

--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
Hi Biff,
Yes, a VERY good explanation. Thanks for your time in doing it.
Regards - Dave.

PS. How do you "mark a thread"?

"T. Valko" wrote:

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!

Counting number of pay period dates between 2 numbers
 

Oh, right. So you can search for it. Sort of like a dog peeing on a post . .
.. :)
Dave.



Above my sig I put a little "marker", "exp101".

Counting number of pay period dates between 2 numbers
 

By the way, even if I reduce the the date range to just a few days (4), when
I highlight <INDIRECT(A1&":"&B1) and press F9, I still get the message
"Formula is too long. Formulas may not exceed 8192 characters."
If I highlight <ROW(INDIRECT(A1&":"&B1)) or <DAY(ROW(INDIRECT(A1&":"&B1)))
and press F9, the result makes sense.
(Yes, XL2007)
Regards - Dave.

Counting number of pay period dates between 2 numbers
 

That is because after the indirect is processed, that part of the expression
becomes something like this... ROW(39667:39670)... each cell in each row
will be iterated through. How many cell are there in a row in XL2007?
Multiply that by 4... how many total cells have you asked the F9 key to
display for you?

Rick


"Dave" wrote in message
...
By the way, even if I reduce the the date range to just a few days (4),
when
I highlight <INDIRECT(A1&":"&B1) and press F9, I still get the message
"Formula is too long. Formulas may not exceed 8192 characters."
If I highlight <ROW(INDIRECT(A1&":"&B1)) or
<DAY(ROW(INDIRECT(A1&":"&B1)))
and press F9, the result makes sense.
(Yes, XL2007)
Regards - Dave.

Counting number of pay period dates between 2 numbers
 

Ah, ok. So in XL2007, even one day would be too much. Thanks for clearing
that up.
Dave.

Counting number of pay period dates between 2 numbers
 

I get the same thing in both Excel 2002, 2007 (although the message is
slightly different in 2007).

Doesn't make any sense since this portion of the formula:

INDIRECT(A1&":"&B1)

Evaluates as: INDIRECT("nnnnn:nnnnn")

However, if you highlight just this portion:

A1&":"&B1

Then it displays as it should: INDIRECT("nnnnn:nnnnn")

Another good tool for doing this is the formula auditing toolsevaluate
formula. I keep this on my QAT in 2007 and on a regular toolbar in 2002.

--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
By the way, even if I reduce the the date range to just a few days (4),
when
I highlight <INDIRECT(A1&":"&B1) and press F9, I still get the message
"Formula is too long. Formulas may not exceed 8192 characters."
If I highlight <ROW(INDIRECT(A1&":"&B1)) or
<DAY(ROW(INDIRECT(A1&":"&B1)))
and press F9, the result makes sense.
(Yes, XL2007)
Regards - Dave.

Counting number of pay period dates between 2 numbers
 

It may have something to do with if you put this...

=INDIRECT("39667:39670")

in a cell, you get an #VALUE! error. Apparently the function call is
meaningless without the ROW function to evaluate the string returned by the
INDIRECT function.

Rick


"T. Valko" wrote in message
...
I get the same thing in both Excel 2002, 2007 (although the message is
slightly different in 2007).

Doesn't make any sense since this portion of the formula:

INDIRECT(A1&":"&B1)

Evaluates as: INDIRECT("nnnnn:nnnnn")

However, if you highlight just this portion:

A1&":"&B1

Then it displays as it should: INDIRECT("nnnnn:nnnnn")

Another good tool for doing this is the formula auditing toolsevaluate
formula. I keep this on my QAT in 2007 and on a regular toolbar in 2002.

--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
By the way, even if I reduce the the date range to just a few days (4),
when
I highlight <INDIRECT(A1&":"&B1) and press F9, I still get the message
"Formula is too long. Formulas may not exceed 8192 characters."
If I highlight <ROW(INDIRECT(A1&":"&B1)) or
<DAY(ROW(INDIRECT(A1&":"&B1)))
and press F9, the result makes sense.
(Yes, XL2007)
Regards - Dave.

Counting number of pay period dates between 2 numbers
 

Yeah, that seems to be the cause. I just tested it in Excel 2002 (less cells
per row, 256 vs 16384).

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
That is because after the indirect is processed, that part of the
expression becomes something like this... ROW(39667:39670)... each cell in
each row will be iterated through. How many cell are there in a row in
XL2007? Multiply that by 4... how many total cells have you asked the F9
key to display for you?

Rick


"Dave" wrote in message
...
By the way, even if I reduce the the date range to just a few days (4),
when
I highlight <INDIRECT(A1&":"&B1) and press F9, I still get the message
"Formula is too long. Formulas may not exceed 8192 characters."
If I highlight <ROW(INDIRECT(A1&":"&B1)) or
<DAY(ROW(INDIRECT(A1&":"&B1)))
and press F9, the result makes sense.
(Yes, XL2007)
Regards - Dave.

Counting number of pay period dates between 2 numbers
 

It seems adding all those extra columns might not be all that helpful after
all; well, at least not as far as array-based formulas and array-entered
formulas which use whole row or whole column references are concerned... all
that extra cell processing will just make such formulas that much more
inefficient.

Rick


"T. Valko" wrote in message
...
Yeah, that seems to be the cause. I just tested it in Excel 2002 (less
cells per row, 256 vs 16384).

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
That is because after the indirect is processed, that part of the
expression becomes something like this... ROW(39667:39670)... each cell
in each row will be iterated through. How many cell are there in a row in
XL2007? Multiply that by 4... how many total cells have you asked the F9
key to display for you?

Rick


"Dave" wrote in message
...
By the way, even if I reduce the the date range to just a few days (4),
when
I highlight <INDIRECT(A1&":"&B1) and press F9, I still get the message
"Formula is too long. Formulas may not exceed 8192 characters."
If I highlight <ROW(INDIRECT(A1&":"&B1)) or
<DAY(ROW(INDIRECT(A1&":"&B1)))
and press F9, the result makes sense.
(Yes, XL2007)
Regards - Dave.

Counting number of pay period dates between 2 numbers
 

Yeah, I'd never use an entire column in an array formula just because it's
easier to type A:A vs A1:A1212.

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
It seems adding all those extra columns might not be all that helpful
after all; well, at least not as far as array-based formulas and
array-entered formulas which use whole row or whole column references are
concerned... all that extra cell processing will just make such formulas
that much more inefficient.

Rick


"T. Valko" wrote in message
...
Yeah, that seems to be the cause. I just tested it in Excel 2002 (less
cells per row, 256 vs 16384).

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote
in message ...
That is because after the indirect is processed, that part of the
expression becomes something like this... ROW(39667:39670)... each cell
in each row will be iterated through. How many cell are there in a row
in XL2007? Multiply that by 4... how many total cells have you asked the
F9 key to display for you?

Rick


"Dave" wrote in message
...
By the way, even if I reduce the the date range to just a few days (4),
when
I highlight <INDIRECT(A1&":"&B1) and press F9, I still get the message
"Formula is too long. Formulas may not exceed 8192 characters."
If I highlight <ROW(INDIRECT(A1&":"&B1)) or
<DAY(ROW(INDIRECT(A1&":"&B1)))
and press F9, the result makes sense.
(Yes, XL2007)
Regards - Dave.