CODEWITHSUNDEEP
excelfunctionexcel-formulaexcel-2010formula
user2486873
user2486873

Reputation: 129

Calculate the Biweekly Pay-period in Excel 2010

I'm trying to come up with a function to calculate the bi-weekly pay-period in Excel. For example: 7/12/11 through 7/26/2011 I want to return Fridays only, 7/21 and 8/5/2011 for Payday. Thanks for your help!

Upvotes: 4

Views: 57237

Answers (2)

Xzila
Xzila

Reputation: 237

GOAL:

Create a formula that will generate a biweekly-expected paydate with any input date.

Define Biweek, & Paydate

The dates supplied at the top are 15 days apart. Biweeks are 14 days. I am assuming that a biweek is on and between Tue 7/12/2011 & Mon 7/25/2011 and that the expected paydate is supposed to be Friday 8/5/2011. I am also assuming that the biweek pattern repeats indefinitely.

  • Example 1: work on 7/11/2011 should be paid on 7/22/2011,
  • Example 2: work on 7/12/2011 should be paid on 8/5/2011,
  • Example 3: work on 7/25/2011 should be paid on 8/5/2011, &
  • Example 4: work on 7/26/2011 should be paid on 8/19/2011.

A biweek is 14 consecutive workdays starting on an arbitrary date S and ending on an arbitrary date E. Where S + 13 = E

An Expected Pay Date (EPD) of a biweek is always a certain number of days after E (The end of the biweek ) The EPD is usually a Friday.

Solutions ( we will assume cell A1 is the input date )

  1. =A1-MOD(A1,14)+IF(MOD(A1,14)<10,20,34) This works by converting every date to the saturday before the input date: A1, this is convenient because the expectated pay date will now always be 20 or 34 days away, the IF formula applies this appropriately.
  2. OR =A1+CHOOSE(MOD(A1,14)+1,20,19,18,17,16,15,14,13,12,11,24,23,22,21) this formulas works on the same subject, but rather than converting everything to saturday it utilizes the fact that with the biweek definition of all biweeks starting on and including a Tuesday and ending 13 days later on and including a Monday the EPD will always be 11 to 25 days off depending on the day of the week it is. The MOD formula determines what day of the week it is and the CHOOSE formula chooses the appropriate number of days to add (11 to 25).

MY COMPANY's BIWEEK & EXPECTED PAY DATE CONFIGURATION:

My company actually has a biweek that starts on a Thursday such that the 14th day of the biweek is a Wednesday. The EPD is always 16 days after the end of the biweek Wednesday. An arbitrary Friday that happens to be one of our EPD's is 12/4/2015. With this information the two formulas above can be adjusted:

  1. =A1-MOD(A1,14)+IF(MOD(A1,14)<5,20,34) again all dates are converted to Saturday, but where it used to be "<10" in the IF formula now it is "<5" because my biweeks end on a Wednesday, instead of a Monday.
  2. =A1+CHOOSE(MOD(A1,14)+1,20,19,18,17,16,29,28,27,26,25,24,23,22,21) same except given my biweek and EPD definition the range is now 16 to 29 days.

HOW TO CHANGE IT TO FIT YOUR DATE SET:

The First formula is probably easiest for most people to use. To adjust it to fit your date Range you'll have to define figure out four things:

  1. d = the input date (this is probably a range like "A1")
  2. s = The difference between your EPD and S (the start of your biweek)
  3. e = The difference between your EPD and E (the end of your biweek)
  4. w = is the MOD(s,14)

APPLY: =d-MOD(d,14)+IF(MOD(d,14)<w,e,s)

Disclaimer: sorry if the maths are a bit complicated looking, if someone has a easier way of explaining I'm always open to it.

Upvotes: 1

barry houdini
barry houdini

Reputation: 46331

If you have a date in A1 then you can return the next payday on or after that date with this formula

=CEILING(A1+1,14)-1

Assuming paydays are every 2 weeks with one payday being on Friday 26 July 2013

If it's the "alternate" Fridays then switch to this version

=CEILING(A1+8,14)-8

Assumes you are using default 1900 date system

Upvotes: 12

Related Questions