Start Day of Bi-Monthly Pay Period

G

Gilham Consulting

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

Glenn

Gilham said:
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))
 
G

Glenn

Gilham said:
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))
 
G

Gilham Consulting

Glenn,

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

Mucho appreciated!

John
 
G

Gilham Consulting

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!
 
G

Glenn

Gilham said:
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),MONTH(A1)+1,0)))))
 
G

Glenn

Glenn said:
=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),MONTH(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),MONTH(A1+5),15),
DATE(YEAR(A1),MONTH(A1),MIN(30,DAY(DATE(YEAR(A1),MONTH(A1)+1,0)))))
 
G

Gilham Consulting

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

Glenn

Gilham said:
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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top