Excel – How to calculate the number of pay days before a date

microsoft excel

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?

Best Answer

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)).

Related Question