Calculate BiWeekly Pay Periods

**gjames** · 10-07-2008, 10:08 AM

I have been working on solving this riddle since yesterday. What I'm trying to do is have Excel use today’s date on the computer to tell me when my Paydays are in a given month. Or have it tell me the next two or three Paydays based on Today's Date.

I can get Excel to calculate Every Two Weeks by using the formula =A3 + 14 then have each sequential field add 14 days to the previous fields date. However what I end up with is just a column of Pay Dates. Not what I'm looking for. I want to have an active page that only shows 2 or 3 future Pay Days.

I want to use the function =TODAY() in my formula so that every time I open up the file it can calculate the next Pay Dates automatically.

I have been playing around with this formula:
=A4=IF(WEEKDAY(EOMONTH(A2,-1)+1,2)=5,EOMONTH(A2,-1)+1,IF(WEEKDAY(EOMONTH(A2,-1)+1,2)<5,EOMONTH(A2,-1)+6-WEEKDAY(EOMONTH(A2,-1)+1,2),IF(WEEKDAY(EOMONTH(A2,-1)+1,2)>5,EOMONTH(A2,-1)+12-WEEKDAY(EOMONTH(A2,-1),2),0)))
All this does is tells me the First Friday of every month. This won't work for me because the Pay Periods are separated by 14 days not the 2nd and 4th Friday of every month.

Here is an example of my Pay Periods: Sep 19, 2008; Oct 3, 2008; Oct 17, 2008; Oct 31, 2008; Nov 14, 2008; Nov 28, 2008; Dec 12, 2008; Dec 26, 2008; etc....

As you can see it can be confusing as in October there are actually 3 Pay periods and then Nov and Dec there are only two.

I tried playing around with a VBA Macro that can tell you which Friday is which but that won't work either.

There needs to be a common starting date that is part of this calculation so that it is only using those pay periods to calculate the results. However what formula could I use that would include Today's Date with this constant to have it calculate the next 2 or 3 Pay Dates?

I believe I have over thought this issue. Could someone help me solve this problem?

**MatrixMan** · 10-07-2008, 10:22 AM

hi - as you point out, you need a baseline reference to your pay cycles other than the current date, which doesn't give you this. I'd suggest - if my assumption about the cycles being in the even weeks of the year is correct - you use the week to determine the next few .. if today's date is in an odd week, it's the following Friday; if an even week, it's the coming Friday. If your annual cycle changes from year to year, then this will only work for one year at a time ... not sure if that helps?

**daddylonglegs** · 10-07-2008, 10:26 AM

This formula will give your next payday

=MOD(DATE(2008,9,19)-TODAY(),14)+TODAY()

The date in the formula can be any payday, past or future. Obviously you can get subsequent paydays by adding 14 to the above.

Note: if today is a payday formula returns today's date

**daddylonglegs** · 10-07-2008, 10:32 AM

Originally Posted by gjames

I have been playing around with this formula:
=A4=IF(WEEKDAY(EOMONTH(A2,-1)+1,2)=5,EOMONTH(A2,-1)+1,IF(WEEKDAY(EOMONTH(A2,-1)+1,2)<5,EOMONTH(A2,-1)+6-WEEKDAY(EOMONTH(A2,-1)+1,2),IF(WEEKDAY(EOMONTH(A2,-1)+1,2)>5,EOMONTH(A2,-1)+12-WEEKDAY(EOMONTH(A2,-1),2),0)))
All this does is tells me the First Friday of every month.

Note: if you do want the first Friday of the month......

=A2-DAY(A2)+8-WEEKDAY(A2-DAY(A2)+2)

**gjames** · 10-07-2008, 11:17 AM

Yes that works perfectly. Thank you for that.

I knew I was overthinking this issue.

I may expand on this later but for now this works.

Thanks again.

**Ltat42a** · 10-19-2008, 09:50 AM

Originally Posted by daddylonglegs

This formula will give your next payday

=MOD(DATE(2008,9,19)-TODAY(),14)+TODAY()

The date in the formula can be any payday, past or future. Obviously you can get subsequent paydays by adding 14 to the above.

Note: if today is a payday formula returns today's date

I have a spreadsheet setup for 2009, I used this formula to show me when my next paydate is. It works good. If I use Jan 2, 2009 as my first payday in 2009, it returns 10-24-2008 (which is my next payday).

Thanx for the tip......LT

**jenc27** · 01-25-2013, 02:13 PM

Originally Posted by daddylonglegs

This formula will give your next payday

=MOD(DATE(2008,9,19)-TODAY(),14)+TODAY()

The date in the formula can be any payday, past or future. Obviously you can get subsequent paydays by adding 14 to the above.

Note: if today is a payday formula returns today's date

I am very new to this, but I think this is exactly what I need. I am not sure where you would add the 14 in though to get the future dates. Then would you just copy and paste the formula into more rows, or would it do it automatically? I need the start date to be 01/25/2013 and go for 2 or 3 years. I am creating a payment plan on my paydays where the payment remains the same.