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.

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))

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))

Glenn,

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

Mucho appreciated!

John

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!

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)))))

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)))))

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.

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!