1

I am trying to determine potential liability in an employment case. For each pay period in which work was performed, each employee is due a penalty amount. Even if they only worked a few days, they might have performed work in two different pay periods, so my formula to count how many 14 day periods there are in each employee's term of employment is not accurate enough.

For each employee I need to determine the number of penalties they are owed, thus I need to know how many pay periods in which they performed work.

I have each employee's hire date (Cell D4) and termination date (Cell E4).

The pay period interval is 14 days (Sunday of Week One to Saturday of Week Two).

The start of the penalty period is 11/9/14, which is the first day of the first pay period, and has a date serial number of 41952.

Can a formula be written to yield the number of pay period end dates in a given span of time?

EDIT

I know the hire date and termination date for each employee in a set of employees.

The pay periods for these employees are each two weeks (Sunday of Week One through Saturday of Week Two). The entire period for my data set is November 9, 2014 to September 7, 2019. There are 126 pay periods total and the first pay period starts on Sunday Nobember 9, 2014).

Based on the dates of employment, for each employee I need to know in how many of those pay periods they were employed. My formula for determining the number of 14 day periods in each employee's term of employment is as follows:

=IF((SUM(D1228-C1228)/14)<=1, "1", ROUNDUP((SUM(D1228-C1228)/14),0))

(Column E is hire date, Column F is termination date)

This was not accurately capturing the number of pay periods in which they worked, just the number of two-week periods between hire and term.

My Solution My not so elegant solution is that I created a look up table ("SOL!$G$2:$G$127") and assigned each pay period a number (1 through 126) and used VLOOKUP to grab the number for the pay periods corresponding to hire date and termination date. I then subtracted the lower number from the higher number and added one to account for the starting pay period.

Formulas to grab hire date pay period and term date pay period numbers. Beginning Pay Period Number:

=IF(C3>=41952,LOOKUP(2,1/(SOL!$F$2:$F$127<=C3)/(SOL!$G$2:$G$127>=C3),SOL!$H$2:$H$127),1)

Ending Pay Period Number:

=IF(C3>=41952,LOOKUP(2,1/(SOL!$F$2:$F$127<=C3)/(SOL!$G$2:$G$127>=C3),SOL!$H$2:$H$127),1)

Formula for calculating total number of pay periods:

=IF(G3=1,0,((G3-F3)+1))

It's not pretty, but it works. Would love to know a more simple solution.

  • My simple suggestion is,, fro better understanding Edit your post and add some sample data along with expected output and the formula you have tried so far, will help us to fix the issue! – Rajesh S Aug 29 '19 at 8:42
  • Could you provide sample file or screenshot about your problem? – Lee Aug 29 '19 at 9:19
  • Quit confusing ,, pay period interval is 14 days (Sunday of Week One to Saturday of Week Two). considering month AUGUST, Sun of Wk 1 is 08/04/19 and Sat of Wk 2 is, 08/17/19 ,, start of the penalty period is 11/9/14, which is the first day of the first pay period, for AUGUST 1st day of pay period is the Sunday 08/04/19 ?? – Rajesh S Aug 29 '19 at 9:20
0

With 09-Nov-2014 (the start of the penalty periods) in C4, hire date in D4 and termination date in E4 try,

=ROUNDUP(SUM(E4, -MAX(C4, D4))/14, 0)

If there are blank termination dates (e.g. still employed) you may need a cutoff date. If the cutoff was TODAY() then change E4 to IF(E4="", TODAY(), E4).

  • Thank you for your quick response. Your solution appears to return the number of 14 day periods between either the penalty period start date or the hire date and the termination date. I have a formula for this, although your formula is much more elegant than what I cobbled together. It doesn't seem to be taking into account the start and end date of each pay period. (i.e. if the hire date is 3/27/15 and the term date is 3/31/15, the employee would have worked only 5 days, but those days would have occurred in two different pay periods (3/15/15-3/28/15 & 3/29/18-4/11/15). – ParalegalMP Aug 28 '19 at 23:45
  • Have a quick and dirty solution. I created a LOOKUP table and assigned each pay period a number between 1 and 126. I used VLOOKUP to grab the pay period number for the hire date and the term date, in a third column I calculated the difference plus one, to determine in how many pay periods work was performed. I don't think it is the most elegant or efficient way to accomplish this, but it is getting me the result I need. – ParalegalMP Aug 29 '19 at 0:55
  • @ParalegalMP: if you found a solution for yourself please post it as an answer, so it can help others too. – Máté Juhász Aug 29 '19 at 14:13
0

Try:

=INT((Termination+7-WEEKDAY(Termination)-dtStart)/14)+1-INT((Hire+1-WEEKDAY(Hire)-dtStart)/14)
  • dtStart: Start of Penalty
  • Hire: Date employee hired
  • Termination: Date employee terminated

If a Termination date might be later than the end of the data set, we might need to make some adjustment.

This works in a similar fashion to your lookup table, but does not require the table.

Algorithm:

  • Adjust hire date to reflect the preceding Sunday
  • Adjust termination date to reflect the following Saturday.
  • Based on startDT, calculate the Hire Date pay period.
  • Based on startDt, calculate the Termination pay period.
  • Calculate the number of pay periods worked.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.