Bi weekly payments per month

D

Don Ray

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.
 
D

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
 
R

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.
Click to expand...

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
 
R

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
Click to expand...

Much simpler than mine


--ron
 
Top