+ Reply to Thread
Results 1 to 3 of 3

Pay roll spread sheet - Match Dates

  1. #1
    Registered User
    Join Date
    08-06-2012
    Location
    Florida, United States
    MS-Off Ver
    Excel 2010
    Posts
    10

    Pay roll spread sheet - Match Dates


    Pay spreadsheet
    I need help on a pay spreadsheet any advice will be greatly appreciated. Here is the general info. The pay periods are based on 14 days, which are pay two weeks after, i.e. pay Period begins on 08/06/2012, ending on 8/19/2012, this pay period is paid on 08/31/2012. The other variants on the pay are:
    1- The employee might had travel in country in any date within the pay period therefore he might had worked the whole 14 days for the period could be 13, 12, 10, 7 days depending on the arriving date.
    2- The travel, training and extension days are paid at $100 per day. The days at Home are not paid.

    In the attached spreadsheet in the schedule tab are the dates and the employee status in reference as to be in country (AF), or Travel (Tvl) Training (Tng) Extension (Ext), if the employes is home (H) then the employee ears nothing (0); I’ve used a MATCH formula, although It works for that particular pay period I would have to select the cells with the respective pay period cells and write the formula manually for each individual pay period, is there a way that I may be able to reference to the pay period beginning and ending dates then count the days either in country (AF), or Travel (Tvl) Training (Tng) Extension (Ext) for each particular pay periods for the rest of the year. Thank you so much.
    Attached Files Attached Files
    Last edited by JBeaucaire; 08-18-2012 at 10:04 AM. Reason: Corrected thread title to topic only, as per forum rules

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

    Re: Pay roll spread sheet help!!!

    1) On your Schedule sheet, the formula in cell E5 could simplify down to:

    =IF(E4=D4, D5+1, 1)

    ...copied to the right.

    2) On the Pay Periods sheet, the formulas would be:

    G2: =COUNTIF(OFFSET(INDEX(Schedule!$3:$3, MATCH('Pay Periods'!$B2, Schedule!$3:$3, 0)),1,,,14),"AF")
    I2: =COUNTIF(OFFSET(INDEX(Schedule!$3:$3, MATCH('Pay Periods'!$B2, Schedule!$3:$3, 0)),1,,,14),"TVL")
    K2: =COUNTIF(OFFSET(INDEX(Schedule!$3:$3, MATCH('Pay Periods'!$B2, Schedule!$3:$3, 0)),1,,,14),"EXT")
    M2: =COUNTIF(OFFSET(INDEX(Schedule!$3:$3, MATCH('Pay Periods'!$B2, Schedule!$3:$3, 0)),1,,,14),"TNG")

    Copy those formulas down.
    _________________
    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
    08-06-2012
    Location
    Florida, United States
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Pay roll spread sheet - Match Dates

    Thank you very much jbeaucaire just simple and easy.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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