+ Reply to Thread
Results 1 to 9 of 9

Payroll Start and End Dates, need Payroll Period fix

  1. #1
    Registered User
    Join Date
    01-27-2014
    Location
    Orlando
    MS-Off Ver
    Excel 2016
    Posts
    49

    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. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    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?
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    01-27-2014
    Location
    Orlando
    MS-Off Ver
    Excel 2016
    Posts
    49

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

  4. #4
    Forum Contributor
    Join Date
    08-14-2013
    Location
    Here and there
    MS-Off Ver
    Excel 2010
    Posts
    376

    Re: Payroll Start and End Dates, need Payroll Period fix

    May I suggest you add an example, please

  5. #5
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    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. #6
    Registered User
    Join Date
    01-27-2014
    Location
    Orlando
    MS-Off Ver
    Excel 2016
    Posts
    49

    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. #7
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Payroll Start and End Dates, need Payroll Period fix

    Quote Originally Posted by colarguns View Post
    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. #8
    Registered User
    Join Date
    01-27-2014
    Location
    Orlando
    MS-Off Ver
    Excel 2016
    Posts
    49

    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. #9
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Payroll help!
    By Jaquie in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 07-18-2014, 02:14 PM
  2. Replies: 2
    Last Post: 05-15-2014, 09:04 AM
  3. Sales in each period between start and end dates
    By pat brown in forum Excel General
    Replies: 1
    Last Post: 07-08-2011, 09:32 AM
  4. Adding payroll stubs payroll calculator
    By Sable in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 08-05-2006, 12:40 PM
  5. [SOLVED] I really need help! Changing work period start dates
    By JLyons in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-16-2005, 09:40 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1