Bi weekly payments per month

I need to be able to produce a number in a cell that reflects how many times
a bi weekly payday such as "2" for 3 paydays if paid on Friady for the month
of April 2005, and "2" for the month of May. Where payments happend to start
April 15, 2005. Thanks in adavnce.

If your start date (15th April 2005) is in A2 and the first day of the month you wish to check is in B2

=2+(DAY($A$2+28+CEILING(B2-$A$2,14))>20)

e.g. in B2 September 1 2005 (format as "mmm-yyyy" if desired)
the above formula will give 3

On Sat, 4 Feb 2006 12:58:52 -0800, "Don Ray" <[email protected]>
wrote:

>I need to be able to produce a number in a cell that reflects how many times
>a bi weekly payday such as "2" for 3 paydays if paid on Friady for the month
>of April 2005, and "2" for the month of May. Where payments happend to start
>April 15, 2005. Thanks in adavnce.

I think this will work.

In the formula below,

A2: Month of Interest as an Excel Date (e.g. 4/15/2005)
PayDay1: A valid PayDay
DOW: Day of the Week (Sun=1, Mon=2, Fri=6, etc)

I believe this formula should work:

=2+(MONTH(MOD(A2-DAY(A2)+8-WEEKDAY(A2-DAY(A2)+8-dow)-
PayDay1,14)+A2-DAY(A2)+8-WEEKDAY(A2-DAY(A2)+8-dow))=
MONTH(MOD(A2-DAY(A2)+8-WEEKDAY(A2-DAY(A2)+8-dow)-
PayDay1,14)+A2-DAY(A2)+8-WEEKDAY(A2-DAY(A2)+8-dow)+28))


--ron

On Sat, 4 Feb 2006 20:15:48 -0600, daddylonglegs
<[email protected]> wrote:

>
>If your start date (15th April 2005) is in A2 and the first day of the
>month you wish to check is in B2
>
>=2+(DAY($A$2+28+CEILING(B2-$A$2,14))>20)
>
>e.g. in B2 September 1 2005 (format as "mmm-yyyy" if desired)
>the above formula will give 3

Much simpler than mine


--ron