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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=508557

On Sat, 4 Feb 2006 12:58:52 -0800, "Don Ray"
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
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