J
janplan
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?
number of pay periods if the person was paid the 1st and 15th of every month?
I
Infinitogool
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.
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.
T
T. Valko
That can be reduced to:
=SUMPRODUCT(--(DAY(ROW(INDIRECT(A1&":"&B1)))={1,15}))
=SUMPRODUCT(--(DAY(ROW(INDIRECT(A1&":"&B1)))={1,15}))
D
Dave
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.
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.
T
T. Valko
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 are:
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
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.
=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 are:
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
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.
T
T. Valko
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
use so I won't have to type that all over again!
exp101
D
Dave
Hi Biff,
Yes, a VERY good explanation. Thanks for your time in doing it.
Regards - Dave.
PS. How do you "mark a thread"?
Yes, a VERY good explanation. Thanks for your time in doing it.
Regards - Dave.
PS. How do you "mark a thread"?
R
Rick Rothstein \(MVP - VB\)
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
=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
T. Valko
You're welcome!
Above my sig I put a little "marker", "exp101".
Above my sig I put a little "marker", "exp101".
D
Dave
Oh, right. So you can search for it. Sort of like a dog peeing on a post . .
..
Dave.
..
Dave.
D
Dave
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.
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.
R
Rick Rothstein \(MVP - VB\)
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
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
D
Dave
Ah, ok. So in XL2007, even one day would be too much. Thanks for clearing
that up.
Dave.
that up.
Dave.
T
T. Valko
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 tools>evaluate
formula. I keep this on my QAT in 2007 and on a regular toolbar in 2002.
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 tools>evaluate
formula. I keep this on my QAT in 2007 and on a regular toolbar in 2002.
R
Rick Rothstein \(MVP - VB\)
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
=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
T. Valko
Yeah, that seems to be the cause. I just tested it in Excel 2002 (less cells
per row, 256 vs 16384).
per row, 256 vs 16384).
R
Rick Rothstein \(MVP - VB\)
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
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
T. Valko
Yeah, I'd never use an entire column in an array formula just because it's
easier to type A:A vs A1:A1212.
easier to type A:A vs A1:A1212.