# Payroll Start and End Dates, need Payroll Period fix

1. ## Payroll Start and End Dates, need Payroll Period fix

I am working a payroll calendar. I need a formula for determining the payroll period. We are paid (bi-weekly). So for illustrative purposes, I have the following:

Cell A1: Start Date
Cell A2: End Date
Cell A3: Payroll Period (help)

We have 26 payroll periods annually. Our Fiscal Year of 2015 started today (10-1-14). Here are actual dates for 2 payroll periods.....
Payroll Period 19 - 9/21/14 thru 10/04/14
Payroll Period 20 - 10/5/14 thru 10/18/14

Will certainly appreciate the help, Thanks!

2. ## Re: Payroll Start and End Dates, need Payroll Period fix

I'm not understanding the problem. If you put a start date in A1 and an End Date in A2, don't you already have the dates defined?

A1: 9/21/14
A2: =A1+6
A3: =TEXT(A1, "MM/DD/YY") & " - " & TEXT(A2, "MM/DD/YY")

Is that what you need?

3. ## Re: Payroll Start and End Dates, need Payroll Period fix

Thank you for your response. I am not looking for defining dates, I am trying to define (payroll period). Let me share a better example, as I wasn't as clear on the first run...my bad.

Cell A1: A date that is manually entered, such as 9-22-14
Cell B1: This is the cell needing a fix. I need the payroll period for the date 9-22-14.

If required, I can produce all 26 payroll periods to the right somewhere, such as....Cell AA1: Start Date
Cell AB1: End Date
Cell AC1: Payroll Period
I know and see payroll periods on paper quite well, but telling Excel to give me a payroll period that falls within the date entered in cell A1 as noted above is my problem. Thanks..

5. ## Re: Payroll Start and End Dates, need Payroll Period fix

So A1 being the only cell you edit:

A1: (manually enter your start date)
B1: =A1+13
A2: =A1+14
B2: =B1+14

Now copy A2:B2 downward 24 more rows.

6. ## Re: Payroll Start and End Dates, need Payroll Period fix

Ok....must be on wrong forum. I have made this quite clear on my issue with the payroll period. You put a date in, and Excel tells you which pay period it is, based off three columns of information, which is placed anywhere on the worksheet. 1st column = start date of payroll period, 2nd column = end date of payroll period, and third column is the payroll period. For those unfamiliar with what a payroll period is, it is a number 1 through 26 which represents a bi-weekly period of time. So you look at cell A1, yep, that is where they enter the date manually. Now look at cell B1, yep that is where the pay period goes. But wait, it doesn't go in there by itself. You need a formula/function to pull the "correct" pay period from those three columns explained above. So in the formula in cell B1, it will do a vlookup or other function to look into the 3 columns of (Date Start, Date End, Payroll Period). It will look at the date in cell A1, and will determine if the date entered in cell A1 falls between two date periods (Start and End dates) in the 3 columns explained. Then it will give you the correct pay period. Now if someone responds with, will you give me an example, or shoots rounds that can't hit a barn door, then move on, because you are wasting my time, and yours as well. Thanks!

7. ## Re: Payroll Start and End Dates, need Payroll Period fix

Originally Posted by colarguns
It will look at the date in cell A1, and will determine if the date entered in cell A1 falls between two date periods (Start and End dates) in the 3 columns explained. Then it will give you the correct pay period.
You may not realize it, or maybe you are on the wrong forum , but that's the first time in this thread you made it clear what you wanted. Obviously you don't believe that, else you wouldn't have scolded us. Funny to my eye.

"How to find a date in a series of pay periods"

VLOOKUP will do the work just fine. You only need the START of each pay period, the END is implied by the start of the next tier. So, assuming your reference list is on Sheet2 in columns A:C, and your formula is on Sheet1 in B1...

A1: Enter your date to find
B1: =VLOOKUP(A1, Sheet2!A:C, 3, 1)

The fourth parameter "1" is the trick. It means "if the date searched is not found, find the closest date below it in column A and return the value from column 3.

Just in case a date might get entered which is too low or too high, I would suggest putting values in the table that give you a response for those as well.

8. ## Re: Payroll Start and End Dates, need Payroll Period fix

Jbeaucaire, this is the first opportunity I have had since your last post to respond. I wish to extend an apology because I was up against a myriad of time constraints in our office during that period of time, and my Marine discipline failed me! I was between a rock and hard place, and you helped pull me out, so thank you! As an FYI, I work for our illustrious government, so let that be a self explanation if you will. Take care, and thanks for your assistance.

9. ## Re: Payroll Start and End Dates, need Payroll Period fix

Ah, been there, been that guy, ate that cookie.

Glad we were able to work it out in the end.
As it appears you've reached a conclusion, I've marked this thread SOLVED for you.
FYI, this is done through the Thread Tools located above the first post in this thread. Thanks.

There are currently 1 users browsing this thread. (0 members and 1 guests)