Start Day of Bi-Monthly Pay Period

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

 

[Glenn]

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

 

[Glenn]

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

 

[Gilham Consulting]

Glenn,

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

Mucho appreciated!

John

 

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

 

[Glenn]

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

 

[Glenn]

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

 

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

 

[Glenn]

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!