# Pay roll spread sheet - Match Dates

1. ## Pay roll spread sheet - Match Dates

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.

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

3. ## Re: Pay roll spread sheet - Match Dates

Thank you very much jbeaucaire just simple and easy.

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

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