Bi weekly payments per month

[Guest]

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.

 

[daddylonglegs]

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

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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