0

I'm working on a budget spreadsheet and I'd like to calculate the number of pay periods before a certain date (including that date if it's a pay day). I'm not really sure how to begin.

My pay periods are always the same length of time, every other Friday. I was thinking I would somehow enter in a pay date, and from there the formula could count the number of pay days from today until another date.

As an example, let's say today is 05/22/2016. My next pay date is 06/03/2016. If I have something due on 12/13/2016, I want the formula to state that there are 14 pay days between now and then. If I were to recalculate from 06/04/2016, then it should say 13.

How can I count the number of pay days between two dates in Excel?

5 Answers 5

1

Here's the equation:

=IF(WEEKDAY(B2)=B1, INT((DAYS(B3,B2)/B4)-1), INT(DAYS(B3,B2)/B4))

Description:

  • The day of the week you get paid needs to be put in cell B1 encoded as Sunday=1, Monday=2, .. ,Saturday=7.

  • The cell B2 should have the starting date.

  • The cell B3 should have the end date.

  • How often you get paid in days needs to go cell B4 (i.e. once per week=7,every other week=14).

  • This will not count the current payday if the start day is a payday. To remove that, just use =INT(DAYS(B3,B2)/B4)).

1

The following formula will count all paydays between the start and end date. It will work whether the start or end date fall on a payday or not:

=SUMPRODUCT(--(B1-ROW(INDIRECT(B3&":"&B4))=CEILING(B1-ROW(INDIRECT(B3&":"&B4)),B2)))
  • Sample Payday - B1 represents any date that falls on a payday, your first payday for example. Be aware, this formula will count paydays that fall before your sample pay day so if you use your first payday, ensure your start date is on or after your first payday.
  • Period - B2 represents the pay period, 7 for weekly or 14 for biweekly.
  • Start Date - B3 The first day of the date range you are querying.
  • End Date - B4 The last day of the date range you are querying.

Example:

  • First payday B1 = 5/8/19
  • Period B2 = 14
  • Start Date B3 = 9/1/2019
  • End Date = B4 = 5/2/2022

The formula would return 69 because there are 69 paydays between 9/1/2019 and 5/2/2022.

1
  • Please explain how this works. … … … … … Please do not respond in comments; edit your answer to make it clearer and more complete. Nov 10, 2019 at 1:33
0

There are functions like WEEKNUM() and WEEKDAY(), and you can just subtract dates form each other to get number of days in between.

Your request is not clear enough for me to give a formula, but for example TODAY()-<somecell> gives you the number of days between them, and WEEKNUM(TODAY())-WEEKNUM(<somecell>) gives the number of weeks between them. Experiment with those.

2
  • As an example, let's say today is 05/22/2016. My next pay date is 06/03/2016. If I have something due on 12/13/2016, I want the formula to state that there are 14 pay days between now and then. If I were to recalculate from 06/04/2016, then it should say 13.
    – ardavis
    May 23, 2016 at 2:14
  • Thanks for the tips though, I'll try to investigate that!
    – ardavis
    May 23, 2016 at 2:14
0

You can't without more information.

For example, if the two dates are:

  • 5/11/2016
  • 5/18/2016

There is a single Friday between these two dates. If you get paid every other Friday, how could anyone tell if that Friday (5/13/2016) is a payday or not ???

2
  • Yes, I agree with you. That's why I think the ultimate answer has two parts to it. If I specify a single pay date, we should be able to figure out if any given Friday is a pay day or not. Yes?
    – ardavis
    May 23, 2016 at 15:05
  • 1
    @ardavis you are 100% correct! May 23, 2016 at 15:38
0

How about this:

=INT((<next_payday>-<final_date>)/14)

with your example:

=INT(("13/12/16"-"3/6/16")/14)

the /14 at the end represents the frequency with which you get paid (every 14 days)

0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .