Start Day of Bi-Monthly Pay Period
 

I have a log of employee time worked (date) in A1. Previously, we had paid
employees weekly...but are now migration to the payperiod of the 15th and
30th of the month.

Currently, I have a formula that determined which what pay period and
returned the starting day of week of A1. For example, 4/16/2009 would
return the first day of the pay period (4/13/2009).

=DATE(YEAR([A1]),MONTH([A1]),DAY([A1]))+0.5-WEEKDAY(DATE(YEAR([A1]),MONTH([A1]),DAY([A1])),2)+1

I would now like the formula to return the pay period date for bimonthly
period and return either 3/30/09 (for A1 values 4/8 or 3/31) or 4/15/09 (for
A1 values 4/15 or 4/29). This formula would ideally tackle February as well
if possible...but not required.

Can anyone provide some insights on how this can be accomplished?

Thanks so much!

John G.

Start Day of Bi-Monthly Pay Period
 

Gilham Consulting wrote:
I have a log of employee time worked (date) in A1. Previously, we had paid
employees weekly...but are now migration to the payperiod of the 15th and
30th of the month.

Currently, I have a formula that determined which what pay period and
returned the starting day of week of A1. For example, 4/16/2009 would
return the first day of the pay period (4/13/2009).

=DATE(YEAR([A1]),MONTH([A1]),DAY([A1]))+0.5-WEEKDAY(DATE(YEAR([A1]),MONTH([A1]),DAY([A1])),2)+1

I would now like the formula to return the pay period date for bimonthly
period and return either 3/30/09 (for A1 values 4/8 or 3/31) or 4/15/09 (for
A1 values 4/15 or 4/29). This formula would ideally tackle February as well
if possible...but not required.

Can anyone provide some insights on how this can be accomplished?

Thanks so much!

John G.

Try this:

=IF(OR(DAY(A1)29,DAY(A1)<15),
DATE(YEAR(A1),MONTH(A1-15),MIN(30,DAY(DATE(YEAR(A1),MONTH(A1+2),0)))),
DATE(YEAR(A1),MONTH(A1),15))

Start Day of Bi-Monthly Pay Period
 

Gilham Consulting wrote:
I have a log of employee time worked (date) in A1. Previously, we had paid
employees weekly...but are now migration to the payperiod of the 15th and
30th of the month.

Currently, I have a formula that determined which what pay period and
returned the starting day of week of A1. For example, 4/16/2009 would
return the first day of the pay period (4/13/2009).

=DATE(YEAR([A1]),MONTH([A1]),DAY([A1]))+0.5-WEEKDAY(DATE(YEAR([A1]),MONTH([A1]),DAY([A1])),2)+1

I would now like the formula to return the pay period date for bimonthly
period and return either 3/30/09 (for A1 values 4/8 or 3/31) or 4/15/09 (for
A1 values 4/15 or 4/29). This formula would ideally tackle February as well
if possible...but not required.

Can anyone provide some insights on how this can be accomplished?

Thanks so much!

John G.


Actually, I missed a "-15" that would come into play in January:

=IF(OR(DAY(A1)29,DAY(A1)<15),
DATE(YEAR(A1-15),MONTH(A1-15),MIN(30,DAY(DATE(YEAR(A1),MONTH(A1+2),0)))),
DATE(YEAR(A1),MONTH(A1),15))

Start Day of Bi-Monthly Pay Period
 

Glenn,

Thank you so much for a great solution to the puzzle.

Mucho appreciated!

John

Start Day of Bi-Monthly Pay Period
 

I have rather large change to the formula based on our pay period.

When the A1 date equalling the 27th to the 11th it return 15th of that month
(2/27 to 3/11=3/15) and the 12th to the 26th return the 30th of that month
(3/12 to 3/26 = 3/30).

Thanks again for your help on this stick situation!

Start Day of Bi-Monthly Pay Period
 

Gilham Consulting wrote:
I have rather large change to the formula based on our pay period.

When the A1 date equalling the 27th to the 11th it return 15th of that month
(2/27 to 3/11=3/15) and the 12th to the 26th return the 30th of that month
(3/12 to 3/26 = 3/30).

Thanks again for your help on this stick situation!





=IF(OR(DAY(A1)26,DAY(A1)<12),DATE(YEAR(A1),MONTH( A1+5),15),
DATE(YEAR(A1),MONTH(A1),MIN(30,DAY(DATE(YEAR(A1),M ONTH(A1)+1,0)))))

Start Day of Bi-Monthly Pay Period
 

Glenn wrote:
Gilham Consulting wrote:
I have rather large change to the formula based on our pay period.

When the A1 date equalling the 27th to the 11th it return 15th of that
month (2/27 to 3/11=3/15) and the 12th to the 26th return the 30th of
that month (3/12 to 3/26 = 3/30).
Thanks again for your help on this stick situation!





=IF(OR(DAY(A1)26,DAY(A1)<12),DATE(YEAR(A1),MONTH( A1+5),15),
DATE(YEAR(A1),MONTH(A1),MIN(30,DAY(DATE(YEAR(A1),M ONTH(A1)+1,0)))))


And then correcting the same error I made last time...


=IF(OR(DAY(A1)26,DAY(A1)<12),DATE(YEAR(A1+5),MONT H(A1+5),15),
DATE(YEAR(A1),MONTH(A1),MIN(30,DAY(DATE(YEAR(A1),M ONTH(A1)+1,0)))))

Start Day of Bi-Monthly Pay Period
 

Worked perfectly...much appreciated Glenn. It allowed me to tie all my
SharePoint lists/mini-applications to a common pay period (group by, with
totals) for days worked or miles submitted.

I was ble to then change the period column in the Microsoft WSS Timecard
template from a "week ending", period to common bi-monthly pay period. The
same formula was used for mileage and sales commissions on our HR portal site.

I am eternally grateful!

John G.

Start Day of Bi-Monthly Pay Period
 

Gilham Consulting wrote:
Worked perfectly...much appreciated Glenn. It allowed me to tie all my
SharePoint lists/mini-applications to a common pay period (group by, with
totals) for days worked or miles submitted.

I was ble to then change the period column in the Microsoft WSS Timecard
template from a "week ending", period to common bi-monthly pay period. The
same formula was used for mileage and sales commissions on our HR portal site.

I am eternally grateful!

John G.


Glad I could help!